☰

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

tag-icon • tag-icon mysql automation sql
更改 MySQL 中所有表的所有列的字符集和排序规则

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

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

谢谢理查德。你真棒！

答案1

首先，不要只听我说！用这个来测试我的建议：

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 时，整个游戏就改变了。你会惊讶地发现它有如此多的用途。

答案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';

但我想如果你真的想要在所有表上执行此操作，您可以使用前者。不过对我来说似乎有点太过分了。:)

答案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` ;

答案4

我最终做了以下事情：

  • 将数据库转储到 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 文件来更改那里的字符集。

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

相关内容