Rails migrations: creating a MySQL BIGINT

add_column :facebook_user_id, :integer, :limit => 8 # BIGINT

Installing the mysql gem on OS X Leopard

First make sure you have the MySQL client libraries installed, then:

sudo env ARCHFLAGS="-arch i386" gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config

Source: http://weblog.rubyonrails.com/2007/10/26/today-is-leopard-day

MySQL: problems creating foreign keys

Things to check:

1. Do you have incompatible types of keys (for example, your foreign key is bigint and reference key is integer(11))
2. Do you have different engines specified for your tables? (for example, parent MyISAM and child InnoDB)
3. Is the column charset of both foreign and reference key the same? e.g. utf8 and utf8, not utf8 and latin1
4. Do the the columns in the foreign key relation have same SIGNED/UNSIGNED attribute?

PHPMyAdmin 2.6.x bug: ISNULL

The SQL parser in PHPMyAdmin 2.6.x (the version used in Plesk 7.5) falls over when confronted with an ISNULL query, for example:

SELECT ISNULL(id) FROM user

The bug is fixed in recent versions of PMA (2.9.x works fine).

FLOAT fields in MySQL 4.1

Warning: Incompatible change: As of MySQL 4.1.2, handling of the FLOAT and DOUBLE floating-point data types is more strict to follow standard SQL. For example, a data type of FLOAT(3,1) stores a maximum value of 99.9. Before 4.1.2, the server allowed larger numbers to be stored. That is, it stored a value such as 100.0 as 100.0. As of 4.1.2, the server clips 100.0 to the maximum allowable value of 99.9. If you have tables that were created before MySQL 4.1.2 and that contain floating-point data not strictly legal for the data type, you should alter the data types of those columns

from http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html

MySQL: Using column aliases and HAVING

It isn’t possible to refer to a column alias in a WHERE clause, but you can use HAVING instead. From http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html:

This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

The WHERE statement is executed to determine which rows should be included in the GROUP BY part, whereas HAVING is used to decide which rows from the result set should be used.

My example using HAVING:

SELECT ms.id, ms.name, COUNT( mpc.page_id ) AS count
FROM map_supplier ms
LEFT JOIN map_profile_content mpc ON ms.id = mpc.map_supplier_id
GROUP BY ms.id
HAVING count > 0

Converting a string to an integer on the fly (for ordering) in MySQL

Use CAST. For example:

SELECT CAST(1 AS UNSIGNED);

Replacing strings in MySQL

Use REPLACE. For example:

REPLACE( haystack , 'find' , 'replace' )

Fetching MySQL rows that start with a number

Use MySQL’s REGEXP support.

SELECT * FROM blah WHERE column REGEXP '^[0-9]+';