New Features In MySQL 6.x

Suppose you use Linux and suppose you have two tables, and their names are alike except for an uppercase / lowercase difference: ‘City’ and ‘CITY’.

Consider the statement
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘City’;

In MySQL 5.0 you’ll see both tables.

In recent versions of MySQL 5.1 you’ll see only one table: ‘City’.

In later versions there’s a proposal — definitely not an accepted proposal, but this blog can be speculative — that the collation for INFORMATION_SCHEMA.TABLES.TABLE_NAME should be changed to reflect this fact. Currently TABLE_NAME is UTF8_GENERAL_CI. If the proposal goes through, then the collation becomes UTF8_BIN for TABLE_NAME and for a few other columns that end with _NAME (but not all of them), except for Falcon tables, but only on some platforms where case is significant. On other platforms, such as Windows, the collation would be a new case-insensitive accent-sensitive collation.

This entry was posted on Monday, May 19th, 2008 at 12:44 am and is filed under Tiny Tweaks. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.