I’m getting error:
I tried manually altering both tables to utf8 general ci,IMPLICIT, but the error persists.
Is there a way to convert all tables to utf8_general_ci,IMPLICIT and be done with it?
Asked by lisovaccaro
You need to execute a alter table statement for each table. The statement would follow this form:
ALTER TABLE tbl_name [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]
Now, to receive a list of all the tables in the database, run the following query:
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="YourDataBaseName" AND TABLE_TYPE="BASE TABLE";
So now you can sit back and let MySQL write the code for you:
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE your_collation_name_here;") AS ExecuteTheString FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="YourDatabaseName" AND TABLE_TYPE="BASE TABLE";
You can copy and execute the results. I haven’t tested the syntax, but the rest should be straightforward. Think of it as a little exercise.
Hope That Helps!
Answered by Namphibian
It’s also a better method to change the collation of varchar columns within the table.
SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') AS mySQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= "myschema" AND TABLE_TYPE="BASE TABLE"
Additionnaly if you have data with forein key on non utf8 column before launch the bunch script use
SET foreign_key_checks = 0;
It means that for mySQL, global SQL will be:
SET foreign_key_checks = 0; ALTER TABLE `table1` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `table2` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `tableXXX` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; SET foreign_key_checks = 1;
However, according to MySQL documentation http://dev.mysql.com/doc/refman/5.1/en/charset-column.html, you should be cautious. ,
EDIT: It just crashes completely enums set, especially with column type enum (even if there is no special caracters) https://bugs.mysql.com/bug.php?id=26731
Answered by Florian HENRY – Scopen
@Namphibian’s advice was quite helpful… However, I went a step farther and added columns and views to the script.
Simply type in the name of your schema below, and it will take care of the rest.
-- set your table name here SET @MY_SCHEMA = ""; -- tables SELECT DISTINCT CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as queries FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=@MY_SCHEMA AND TABLE_TYPE="BASE TABLE" UNION -- table columns SELECT DISTINCT CONCAT("ALTER TABLE ", C.TABLE_NAME, " CHANGE ", C.COLUMN_NAME, " ", C.COLUMN_NAME, " ", C.COLUMN_TYPE, " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as queries FROM INFORMATION_SCHEMA.COLUMNS as C LEFT JOIN INFORMATION_SCHEMA.TABLES as T ON C.TABLE_NAME = T.TABLE_NAME WHERE C.COLLATION_NAME is not null AND C.TABLE_SCHEMA=@MY_SCHEMA AND T.TABLE_TYPE="BASE TABLE" UNION -- views SELECT DISTINCT CONCAT("CREATE OR REPLACE VIEW ", V.TABLE_NAME, " AS ", V.VIEW_DEFINITION, ";") as queries FROM INFORMATION_SCHEMA.VIEWS as V LEFT JOIN INFORMATION_SCHEMA.TABLES as T ON V.TABLE_NAME = T.TABLE_NAME WHERE V.TABLE_SCHEMA=@MY_SCHEMA AND T.TABLE_TYPE="VIEW";
Answered by dGo
The more precise query is as follows. I’ll show you how to convert it to utf8 as an example.
SELECT CONCAT("ALTER TABLE `", TABLE_NAME,"` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;") AS mySQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="myschema" AND TABLE_TYPE="BASE TABLE"
Answered by Pankaj
You can now do the following with PhpMyAdmin:
I needed to convert over 250 tables. It took a little more than 5 minutes to complete.
Answered by Mindsect Team
Post is based on https://stackoverflow.com/questions/10859966/how-to-convert-all-tables-in-database-to-one-collation