将所有表和字段更改为MYSQL中utf-8-bin排序规则的脚本 [英] A script to change all tables and fields to the utf-8-bin collation in MYSQL

问题描述

是否可以运行一个SQL或PHP脚本来更改数据库中所有表和字段的默认排序规则?

Is there a SQL or PHP script that I can run that will change the default collation in all tables and fields in a database?

我可以自己写一个，但是我认为这应该是可以在这样的站点上容易获得的东西.如果我可以在有人发布一个之前自己提出一个，我会自己发布.

I can write one myself, but I think that this should be something that readily available at a site like this. If I can come up with one myself before somebody posts one, I will post it myself.

推荐答案

小心！如果您实际上已将utf存储为另一种编码，则手上可能会遇到麻烦.先备份.然后尝试一些标准方法:

Be careful! If you actually have utf stored as another encoding, you could have a real mess on your hands. Back up first. Then try some of the standard methods:

例如 http://www.cesspit.net/drupal/node/898 http://www.hackszine.com/blog/archive/2007/05 /mysql_database_migration_latin.html

我不得不求助于将所有文本字段转换为二进制，然后再转换回varchar/text.这救了我的屁股.

I've had to resort to converting all text fields to binary, then back to varchar/text. This has saved my ass.

我的数据是UTF8，存储为latin1.我做了什么:

I had data is UTF8, stored as latin1. What I did:

下降索引. 将字段转换为二进制. 转换为utf8-genci

Drop indexes. Convert fields to binary. Convert to utf8-general ci

如果您使用的是LAMP，请不要忘记在与数据库进行交互之前添加set NAMES命令，并确保设置了字符编码标头.

If your on LAMP, don’t forget to add set NAMES command before interacting with the db, and make sure you set character encoding headers.

这篇关于将所有表和字段更改为MYSQL中utf-8-bin排序规则的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！