By now, we’re all aware that a utf8 charset and collation is the “right” thing to do, with most folks opting for utf8_general_ci or utf8_unicode_ci. However, lots of installations still default to some form of latin1, which is unfortunate. There are multiple ways to do this, all which functionally do the same thing. Be aware ..
Suppose I try to create a table with a primary key that’s varchar(500), and MySQL complains the key length is longer than the maximum of 1000 bytes. 500 is less than 1000. What’s happening? Plus, a tasty (yet apparently harmless) bug in MySQL. Here’s a statement that will fail on most servers: CREATE TABLE test(c ..
CONVERT ALL THE THINGS. Well, almost all the things. #!/bin/bash for database in aaaaa bbbbb ccccc ddddd eeee do thesetables=`mysql -N -B -e “SELECT CONCAT(TABLE_SCHEMA,’.’,TABLE_NAME) \ FROM information_schema.TABLES where TABLE_SCHEMA = \”$database\”” \ AND TABLE_SCHEMA NOT IN (‘mysql’,’information_schema’,’performance_schema’)` alltables=`echo $alltables $thesetables` # change the schema itself mysql -e “ALTER DATABASE $database CHARACTER SET utf8 COLLATE ..
If ever there was a TL;DR, this is it. However, it is important to understand the “whys” in my world, so here goes. A full discussion of character sets and encoding is beyond the scope of this document. (If you want more background, I recommend checking out the wikipedia article for a good place to ..