0
SELECT CONCAT("ALTER TABLE ", TABLE_NAME," COLLATE your_collation_name_here;") AS ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDatabaseName"
AND TABLE_TYPE="BASE TABLE"
Reste à copier-coller les requêtes générées… Exemple pour WordPress :
ALTER TABLE wp_commentmeta COLLATE utf8_general_ci;
ALTER TABLE wp_comments COLLATE utf8_general_ci;
ALTER TABLE wp_links COLLATE utf8_general_ci;
ALTER TABLE wp_options COLLATE utf8_general_ci;
ALTER TABLE wp_postmeta COLLATE utf8_general_ci;
ALTER TABLE wp_posts COLLATE utf8_general_ci;
ALTER TABLE wp_term_relationships COLLATE utf8_general_ci;
ALTER TABLE wp_term_taxonomy COLLATE utf8_general_ci;
ALTER TABLE wp_termmeta COLLATE utf8_general_ci;
ALTER TABLE wp_terms COLLATE utf8_general_ci;
ALTER TABLE wp_usermeta COLLATE utf8_general_ci;
ALTER TABLE wp_users COLLATE utf8_general_ci;
Une autre possibilité issue de StackOverlow, que je ne prends même pas la peine de traduire :
I have a solution that will convert databases and tables by running a few commands. It also converts all columns of the type varchar, text, tinytext, mediumtext, longtext. You can add other types like char by simply extending the queries in the following code. You should also backup your database in case something breaks.
Copy the following code into a file called it preAlterTables.sql:
use information_schema;
SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;") as _sql
FROM `TABLES` where table_schema like "yourDbName" group by table_schema;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;") as _sql
FROM `TABLES` where table_schema like "yourDbName" group by table_schema, table_name;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8 COLLATE utf8_unicode_ci;") as _sql
FROM `COLUMNS` where table_schema like "yourDbName" and data_type in ('varchar');
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8 COLLATE utf8_unicode_ci;") as _sql
FROM `COLUMNS` where table_schema like "yourDbName" and data_type in ('text','tinytext','mediumtext','longtext');
Replace all occurences of "yourDbName" with the database you want to convert. Then run:
mysql -uroot < preAlterTables.sql | egrep '^ALTER' > alterTables.sql
This will generate a new file alterTables.sql, with all the queries you need to convert the database. Run the following command to start the conversion:
mysql -uroot < alterTables.sql
You can also adapt this to run through multiple databases, by changing the condition for the table_schema. For example table_schema like "wiki_%" will convert all databases with the name prefix wiki_. To convert all databases replace the condition with table_type!='SYSTEM VIEW'.
An issue that might arise. I had some varchar(255) columns in mysql keys. This causes an error:
ERROR 1071 (42000) at line 2229: Specified key was too long; max key length is 767 bytes
If that happens you can simply change the column to be smaller, like varchar(150), and rerun the command.
Please note: This answer converts the database to utf8_unicode_ci instead of utf8_bin, asked in the question. But you can simply replace this.
Source: https://dba.stackexchange.com/a/104866/93872
DATE 16 Mar 2016
