主页 / server / 问题 / 65043
Accepted
The Disintegrator
Asked: 2009-09-14 21:39:21 +0800 CST

更改 MySQL 中所有表的所有列中的字符集和排序规则

  • 772

我需要在所有表的所有列中执行这些语句。

alter table table_name charset=utf8;
alter table table_name alter column column_name charset=utf8;

是否可以在 MySQL 中以任何方式自动执行此操作？我宁愿避免使用 mysqldump

更新：Richard Bronosky 给我指路了 :-)

我需要在每个表中执行的查询：

alter table DBname.DBfield CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

生成所有其他查询的疯狂查询：

SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, '  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DBname';

我只想在一个数据库中执行它。一次执行所有操作花费的时间太长了。事实证明，它为每个表的每个字段生成一个查询。并且每个表只需要一个查询（与救援不同）。获取文件的输出是我实现它的方式。

如何将输出生成到文件：

mysql -B -N --user=user --password=secret -e "SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, '  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DBname';" > alter.sql

最后执行所有查询：

mysql --user=user --password=secret < alter.sql

谢谢理查德。你就是那个男人！

mysql

6 个回答

  1. Best Answer

    首先，不要只相信我的话！用这个测试我的建议：

    select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' limit 10; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema' limit 10;

    如果您对此结果感到满意，请删除限制子句并将输出保存到 SQL 脚本，或者，像我在此处演示的那样，将输出直接通过管道传输到 mysql 。看起来像这样：

    mysql -B -N --host=prod-db1 --user=admin --password=secret -e "select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema'; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema';" | mysql --host=prod-db1 --user=admin --password=secret

    当您开始考虑使用有效 SQL 来生成有效 SQL 时，它会改变整个游戏。你会惊讶于你发现它有多少用途。

    • 20

  2. 实际上，您可以在表上使用 CONVERT TO 来让它将该表中的所有列转换为字符集和排序规则。

    SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'databasename';

    此外，对我来说，选择您要在其上执行此操作的实际数据库更有意义。所以这：

    ... WHERE TABLE_SCHEMA = 'databasename';

    而不是这个：

    ... WHERE TABLE_SCHEMA != 'information_schema';

    但我想如果你真的想在所有桌子上都这样做，你可以使用前者。不过对我来说似乎有点沉重。:)

    • 8

  3. 要更改我使用的所有列的排序规则

    SELECT CONCAT(  'ALTER TABLE ',  `TABLE_NAME` ,  ' CHANGE `',  `COLUMN_NAME` ,  '` `',`COLUMN_NAME` ,  '` ',  `DATA_TYPE` ,  '(',  `CHARACTER_MAXIMUM_LENGTH` ,  ') CHARACTER SET utf8 COLLATE utf8_swedish_ci ;' ) FROM  `COLUMNS` WHERE  `TABLE_SCHEMA` =  <schema> AND  `COLLATION_NAME` !=  'utf8_swedish_ci' ORDER BY  `TABLE_NAME` ,  `ORDINAL_POSITION` ;
    • 2

  4. 您可以使用information_schema数据库来查找您必须更改的列和表。您可以通过以下方式找到它们：

    SELECT table_name, column_name FROM information_schema.`COLUMNS`
WHERE table_schema='your database' AND collation_name LIKE 'latin%';

    然后，您可以使用 SQL 脚本、存储过程或您喜欢的开发语言自动执行更改。

    • 0

  5. 我最终做了以下事情：

    • 将数据库转储到 sql 文件，使用mysqldump db_name > db_name.sql
    • 使用 awk 替换 sql 文件中的字符集sed -i -e 's/old_charset/new_charset/g' db_name.sql
    • 导入这个新的数据库文件mysql < db_name

    我不会在生产中使用它，但我需要在本地开发环境中使用它。

    或者，我可以分别转储 SQL 结构和数据，然后进入 SQL 文件以更改那里的字符集。

    请记住，如果您在不兼容的字符集之间进行转换，这可能会弄乱您的数据。

    • 0

  6. 更改字符集 = utf8mb3 的所有表中的所有列

    从 information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DB NAME HERE' 和 CHARACTER_SET_NAME = 'utf8mb3'中选择 concat('ALTER TABLE ',TABLE_Name, 'MODIFY ',COLUMN_NAME, '' , COLUMN_TYPE, 'CHARACTER SET utf8mb4;') 命令；

    然后运行结果。

    • 0

相关问题