How to convert all tables into a database in one sort?

Question

I get an error message:

Invalid combination of sorts (utf8_general_ci, IMPLICIT) and (utf8_unicode_ci, IMPLICIT) for operation '=' "

I tried changing both tables manually to utf8_general_ci,IMPLICIT , but I still get the error.

Is there a way to convert all tables to utf8_general_ci,IMPLICIT and do with it?

Answer 1

You need to execute the alter table statement for each table. The application will follow this form:

 ALTER TABLE tbl_name [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name] 

Now, to get all the tables in the database, you need to execute the following query:

 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="YourDataBaseName" AND TABLE_TYPE="BASE TABLE"; 

So now MySQL will write you the code:

 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 the results and execute them. I have not tested the syntax, but you should be able to figure out the rest. Think of it as a little exercise.

Hope this helps!

Answer 2

The best option to change also sort the varchar columns inside the table is also

 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" 

Addition, if you have data with the forein key on the non utf8 column before running, a bunch of script uses

 SET foreign_key_checks = 0; 

This means that global SQL will be for mySQL:

 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; 

But be careful if according to the mysql documentation http://dev.mysql.com/doc/refman/5.1/en/charset-column.html ,

If you use ALTER TABLE to convert a column from one character set to another, MySQL tries to match the data values, but if the character sets are incompatible, there may be data loss. "

EDIT: Especially with an enumeration of a column type, it just ends with the completion of the enumeration (even if there are no special characters) https://bugs.mysql.com/bug.php?id=26731

Answer 3

@ The Namphibian proposal helped me a lot ...
went a little further and added columns and views to the script

just enter your schema name below and it will do 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"; 

Answer 4

Below is a more accurate query. I will give an example of how to convert it to utf8

 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" 

Answer 5

You can use this BASH script:

 #!/bin/bash USER="YOUR_DATABASE_USER" PASSWORD="YOUR_USER_PASSWORD" DB_NAME="DATABASE_NAME" CHARACTER_SET="utf8" # your default character set COLLATE="utf8_general_ci" # your default collation tables=`mysql -u $USER -p$PASSWORD -e "SELECT tbl.TABLE_NAME FROM information_schema.TABLES tbl WHERE tbl.TABLE_SCHEMA = '$DB_NAME' AND tbl.TABLE_TYPE='BASE TABLE'"` for tableName in $tables; do if [[ "$tableName" != "TABLE_NAME" ]] ; then mysql -u $USER -p$PASSWORD -e "ALTER TABLE $DB_NAME.$tableName DEFAULT CHARACTER SET $CHARACTER_SET COLLATE $COLLATE;" echo "$tableName - done" fi done 

Answer 6

If you want to copy-paste a bash script:

 var=$(mysql -e 'SELECT CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_czech_ci;") AS execTabs FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="zabbix" AND TABLE_TYPE="BASE TABLE"' -uroot -p ) var+='ALTER DATABASE zabbix CHARACTER SET utf8 COLLATE utf8_general_ci;' echo $var | cut -d " " -f2- | mysql -uroot -p zabbix 

Change zabbix to your database name.

Answer 7

This is my version of bash script. It takes the database name as a parameter and converts all tables to a different encoding and sorting (set by other parameters or the default value defined in the script).

 #!/bin/bash # mycollate.sh <database> [<charset> <collation>] # changes MySQL/MariaDB charset and collation for one database - all tables and # all columns in all tables DB="$1" CHARSET="$2" COLL="$3" [ -n "$DB" ] || exit 1 [ -n "$CHARSET" ] || CHARSET="utf8mb4" [ -n "$COLL" ] || COLL="utf8mb4_general_ci" echo $DB echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql echo "USE $DB; SHOW TABLES;" | mysql -s | ( while read TABLE; do echo $DB.$TABLE echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB done )