- MYSQL CREATE DATABASE UTF8 INNODB HOW TO
- MYSQL CREATE DATABASE UTF8 INNODB MANUAL
Replacing with table_schema LIKE "wiki_%" would convert all databases whose name starts with wiki_. To run the conversion on multiple databases adjust the table_schema LIKE "YOUR_DATABASE_NAME" part of the query, for example:. Run the following command to run the queries, performing the conversion: mysql -u root -p < queries.sql Run the following command to generate a new file queries.sql, with all the queries you need to convert the database.: mysql -u root -p -s queries.sql SELECT CONCAT("ALTER TABLE `",`COLUMNS`.table_schema,"`.`",`COLUMNS`.table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",IF(is_nullable="YES"," NULL"," NOT NULL")," ") AS _sqlįROM `COLUMNS` INNER JOIN `TABLES` ON `TABLES`.table_name = `COLUMNS`.table_name WHERE `COLUMNS`.table_schema like "YOUR_DATABASE_NAME" and data_type in ('text','tinytext','mediumtext','longtext') AND TABLE_TYPE='BASE TABLE' SELECT CONCAT("ALTER TABLE `",`COLUMNS`.table_schema,"`.`",`COLUMNS`.table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",IF(is_nullable="YES"," NULL"," NOT NULL")," ") AS _sqlįROM `COLUMNS` INNER JOIN `TABLES` ON `TABLES`.table_name = `COLUMNS`.table_name WHERE `COLUMNS`.table_schema like "YOUR_DATABASE_NAME" and data_type in ('varchar','char') AND TABLE_TYPE='BASE TABLE' UNION SELECT CONCAT("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ") AS _sqlįROM `TABLES` WHERE table_schema LIKE "YOUR_DATABASE_NAME" AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema, table_name UNION 
SELECT CONCAT("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ") AS _sqlįROM `TABLES` WHERE table_schema LIKE "YOUR_DATABASE_NAME" AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema UNION You should always backup your database in case something goes wrong.Ĭopy the following query into gen_queries.sql, replacing the 4 occurrences of YOUR_DATABASE_NAME with the name of the database you wish to convert: USE information_schema It converts all columns of the type varchar, text, tinytext, mediumtext, longtext, char. This solution will generate and then run queries needed to convert databases, tables and columns.
MYSQL CREATE DATABASE UTF8 INNODB MANUAL
Section 10.1.11 of the MySQL 5.5 Reference Manual has some more information on this. As described in step 4 of the abovementioned guide, you’ll need to check the maximum length of columns and index keys, as the number you specify has a different meaning when utf8mb4 is used instead of utf8. Note, however, that you cannot fully automate the conversion from utf8 to utf8mb4.
The above line is just an example for a VARCHAR column.) (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties.
MYSQL CREATE DATABASE UTF8 INNODB HOW TO
From my guide How to support full Unicode in MySQL databases, here are the queries you can run to update the charset and collation of a database, a table, or a column:
0 kommentar(er)
