Problem
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
Solution #1
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
Solution #2
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
Solution #3
@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
Solution #4
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
Solution #5
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