MySQL 如何将数据库中的所有表转换为一个排序规则？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10859966/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert all tables in database to one collation?
提问by lisovaccaro
I'm getting error:
我收到错误：
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='"
非法混合排序规则 (utf8_general_ci,IMPLICIT) 和 (utf8_unicode_ci,IMPLICIT) 用于操作“=”
I tried changing both tables manually to utf8_general_ci,IMPLICITbut I'm still getting the error.
我尝试将两个表手动更改为，utf8_general_ci,IMPLICIT但仍然出现错误。
Is there a way to convert all tables to utf8_general_ci,IMPLICITand be done with it?
有没有办法将所有表转换为utf8_general_ci,IMPLICIT并完成它？
回答by Namphibian
You need to execute a alter table statement for each table. The statement would follow this form:
您需要为每个表执行一个alter table 语句。该声明将遵循以下形式：
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:
那么现在让 MySQL 为您编写代码：
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!
希望有帮助！
回答by Florian HENRY - ATM Consulting
Better option to change also collation of varchar columns inside table also
更好的选择也可以更改表内 varchar 列的排序规则
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
另外，如果您在启动 bundle 脚本之前在非 utf8 列上有带有 forein 键的数据
SET foreign_key_checks = 0;
It means global SQL will be for mySQL :
这意味着全局 SQL 将用于 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,
但如果根据 mysql 文档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. "
如果使用 ALTER TABLE 将列从一种字符集转换为另一种字符集，MySQL 会尝试映射数据值，但如果字符集不兼容，则可能会丢失数据。”
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
编辑：特别是列类型枚举，它只是完全崩溃枚举集（即使没有特殊字符） https://bugs.mysql.com/bug.php?id=26731
回答by dGo
@Namphibian's suggestion helped me a lot...
went a little further though and added columns and views to the script
@Namphibian 的建议对我有很大帮助...
虽然更进一步，并向脚本添加了列和视图
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";
回答by Pankaj
Below is the more accurate query. I am giving example how to convert it to utf8
下面是更准确的查询。我正在举例说明如何将其转换为 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"
回答by Lukas Brzak
You can use this BASH script:
你可以使用这个 BASH 脚本：
#!/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
回答by Calibra
For phpMyAdmin I figured this out:
对于 phpMyAdmin 我想出了这个：
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.
只需更改您的tableschemaname 就可以了。
回答by Petr Stastny
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).
这是我的 bash 脚本版本。它将数据库名称作为参数并将所有表转换为另一个字符集和排序规则（由脚本中定义的另一个参数或默认值给出）。
#!/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=""
CHARSET=""
COLL=""
[ -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
)
回答by G H
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.
通过添加密码变量，进一步从@Petr Stastny 得到答案。我更喜欢它是否真的像普通密码一样输入而不是作为参数输入，但它可以满足我的需要。
#!/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=""
PW=""
CHARSET=""
COLL=""
[ -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"
回答by Tom Gould
Following on from G H I've added the user and host parameters incase you need to do this on a remote server
在 GH 之后，我添加了用户和主机参数，以防您需要在远程服务器上执行此操作
#!/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=""
USER=""
PW=""
HOST=""
CHARSET=""
COLL=""
[ -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"
回答by Xdg
If you want a copy-paste bash script:
如果你想要一个复制粘贴的 bash 脚本：
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.
将 zabbix 更改为您的数据库名称。
