Multi-Column Primary Key in MySQL 5

Quoted from the CREATE TABLE Syntax page: A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, …) clause. Something like this can be …

Read more

How to count items in comma separated list MySQL

There is no built-in function that counts occurences of substring in a string, but you can calculate the difference between the original string, and the same string without commas: LENGTH(fooCommaDelimColumn) – LENGTH(REPLACE(fooCommaDelimColumn, ‘,’, ”)) It was edited multiple times over the course of almost 8 years now (wow!), so for sake of clarity: the query …

Read more

Remove trailing zeros in decimal value with changing length

Easiest way by far, just add zero! Examples: SET @yournumber1=”1.500″, @yournumber2=”23.030″, @yournumber3=”2.000″, @yournumber4=”4.450″ ; SELECT (@yournumber1+0), (@yournumber2+0), (@yournumber3+0), (@yournumber4+0) ; +——————+——————+——————+——————+ | (@yournumber1+0) | (@yournumber2+0) | (@yournumber3+0) | (@yournumber4+0) | +——————+——————+——————+——————+ | 1.5 | 23.03 | 2 | 4.45 | +——————+——————+——————+——————+ 1 row in set (0.00 sec) If the column your value comes from is …

Read more

Is there a way to ‘listen’ for a database event and update a page in real time?

This isn’t too difficult. The simple way would be to add via .append: $( ‘#table > tbody:last’).append(‘<tr id=”id”><td>stuff</td></tr>’); Adding elements real-time isn’t entirely possible. You’d have to run an Ajax query that updates in a loop to “catch” the change. So, not totally real-time, but very, very close to it. Your user really wouldn’t notice …

Read more

Maximum MySQL user password length

This is the reference pages I found when I googled : Link1 and Link2 If you are using MySQL Replication, be aware that, currently, a password used by a replication slave as part of a CHANGE MASTER TO statement is effectively limited to 32 characters in length; if the password is longer, any excess characters …

Read more