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
./configure --with-charset=utf8 --with-collation=utf8_general_ci
or alter the way the mysqld service starts:
mysqld --character-set-server=utf8 --collation-server=utf8_general_ci
or just convert your databases:
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
