如何将数据库中的所有表转换为一种排序规则？

我错了:

对于操作“ =’”，非法混合排序规则(utf8 _ general _ ci，IMPLICIT)和(utf8 _ unicode _ ci，IMPLICIT)

我尝试手动更改两个表为 utf8_general_ci,IMPLICIT，但我仍然得到错误。

有没有一种方法可以将所有的表转换成 utf8_general_ci,IMPLICIT并完成它？

133374 次浏览
小开
最佳答案

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 get all the tables in the database you would need to execute the following query:

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

So now 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 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 That Helps!

小开

Below is the more accurate query. I am giving example 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"
小开

Better option to change also collation of varchar columns inside table 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"

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 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 take care if according 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 attempts to map the data values, but if the character sets are incompatible, there may be data loss. "

EDIT: Specially with column type enum, it just crash completly enums set (even if there is no special caracters) https://bugs.mysql.com/bug.php?id=26731

小开

If you want a copy-paste 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.

小开

@Namphibian's suggestion helped me a lot...
went a little further though and added columns and views to the script

just enter your schema's 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";
小开

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
小开

This is my version of a bash script. It takes database name as a parameter and converts all tables to another charset and collation (given by another parameters or 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
)
小开

Taking the answer from @Petr Stastny a step further by adding a password variable. I'd prefer if it actually took it in like a regular password rather than as an argument, but it's working for what I needed.

#!/bin/bash


# mycollate.sh <database> <password> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables


DB="$1"
PW="$2"
CHARSET="$3"
COLL="$4"


[ -n "$DB" ] || exit 1
[ -n "$PW" ]
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_bin"


PW="--password=""$PW"


echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql -u root "$PW"


echo "USE $DB; SHOW TABLES;" | mysql -s "$PW" | (
while read TABLE; do
echo $DB.$TABLE
echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql "$PW" $DB
done
)


PW="pleaseEmptyMeNow"
小开

Following on from G H I've added the user and host parameters incase you need to do this on a remote server

    #!/bin/bash


# mycollate.sh <database> <user> <password> [<host> <charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables


DB="$1"
USER="$2"
PW="$3"
HOST="$4"
CHARSET="$5"
COLL="$6"


[ -n "$DB" ] || exit 1
[ -n "$USER" ] || exit 1
[ -n "$PW" ] || exit 1
[ -n "$HOST" ] || HOST="localhost"
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_general_ci"


PW="--password=""$PW"
HOST="--host=""$HOST"
USER="--user=""$USER"


echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql "$HOST" "$USER" "$PW"


echo "USE $DB; SHOW TABLES;" | mysql  "$HOST" "$USER" "$PW" | (
while read TABLE; do
echo $DB.$TABLE
echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql  "$HOST" "$USER" "$PW" $DB
done
)


PW="pleaseEmptyMeNow"
小开

For phpMyAdmin I figured this out:

SELECT GROUP_CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" SEPARATOR ' ') AS    OneSQLString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="yourtableschemaname"
AND TABLE_TYPE="BASE TABLE"

Just change yourtableschemaname and you're fine.

小开

If you're using PhpMyAdmin, you can now:

  1. Select the database.
  2. Click the "Operations" tab.
  3. Under "Collation" section, select the desired collation.
  4. Click the "Change all tables collations" checkbox.
  5. A new "Change all tables columns collations" checkbox will appear.
  6. Click the "Change all tables columns collations" checkbox.
  7. Click the "Go" button.

I had over 250 tables to convert. It took a little over 5 minutes.

小开

I will share my answer using MySQL procedure. You need to run 3 sql command.

1.

DROP PROCEDURE IF EXISTS UpdateTable;

2.

DELIMITER $$


CREATE PROCEDURE UpdateTable()
BEGIN


DECLARE done INT DEFAULT FALSE;
DECLARE _table_name CHAR(255);
DECLARE cur CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'my_db_name' AND table_type = "BASE TABLE";
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


OPEN cur;
My_loop: LOOP
FETCH cur INTO _table_name;
SET @my_table_name = _table_name;


IF done THEN
LEAVE My_loop;
END IF;


SET FOREIGN_KEY_CHECKS = 0;


SET @stmt = CONCAT('ALTER TABLE ', @my_table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;');
PREPARE stmt1 FROM @stmt;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
        

SET FOREIGN_KEY_CHECKS = 1;
            

END LOOP;
CLOSE cur;


END$$


DELIMITER ;

3.

CALL UpdateTable();

Then run first one again. If you don't want to store the procedure.


提交答案