MySQL Collation and Character Set Notes

When I was importing SQL data files into MySQL 5 (exported from MySQL 3.23) I ran into problems with collation types receiving the following error:

ERROR 1071 (42000) at line 4659: Specified key was too long; max key length is 1000 bytes 

"Collation" refers to letter sorting order – and you usually select it based on the typical letter usage for the database. Since MySQL is founded in Sweden, the default is Swedish as presented in the Latin-1 character set. ("ci" = character index, "bin" = binary).

 Importing Data Files ito MySQL

mysql -u theuser -pthepassword thedbname < thesqlcode.sql

You should be just fine with the "latin1-swedish-ci" collation. I ran into confusing problems when I tried to switch phpBB from "latin1-swedish-ci" to "utf8-general-ci". Basically I ended up making utf8-general-ci the default for the whole MySQL server – in part by modifying /etc/my.cnf


[safe_mysqld]
default-character-set=utf8

[client]
default-character-set=utf8

Well, you can always recompile MySQL and change that:

Code:
./configure --with-charset=utf8 --with-collation=utf8_general_ci

or alter the way the mysqld service starts:

Code:
 
mysqld --character-set-server=utf8 --collation-server=utf8_general_ci

or just convert your databases:

Code:
ALTER DATABASE `myDB` CHARACTER SET utf8 COLLATE utf8_general_ci;
// run the following query on each table:
ALTER TABLE `tableName` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>