blocks|key|1536934|text|注意!如果你真的将utf存储为另一种编码，你的手头可能会变得一团糟。先后退。然后尝试一些标准方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1536935|例如http://www.cesspit.net/drupal/node/898+http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html|offset|length|1536936|我不得不将所有文本字段转换为二进制，然后再转换回varchar/text。这救了我的命。|1536937|我的数据是以latin1格式存储的UTF8。我做了什么：|1536938|删除索引。将字段转换为二进制。转换为UTF8-通用配置项|1536939|如果您的on+LAMP，在与数据库交互之前，不要忘记添加set+NAMES命令，并确保您设置了字符编码标头。|1536940|entityMap|0|LINK|mutability|MUTABLE|url|http://www.cesspit.net/drupal/node/898|1|http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html^0|0|2|12|0|15|29|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@$D|Z|E|10|1|11]|$D|12|E|13|1|14]]|A|$]]|$1|F|3|G|5|6|7|15|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|16|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|17|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|18|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|O|$P|$5|Q|R|S|A|$T|U]]|V|$5|Q|R|S|A|$T|W]]]]

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:

for instance
<a href="http://www.cesspit.net/drupal/node/898" rel="noreferrer">http://www.cesspit.net/drupal/node/898</a>
<a href="http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html" rel="noreferrer">http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html</a>

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

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

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

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.

blocks|key|1751946|text|此PHP代码段将更改数据库中所有表的排序规则。(它取自this+site。)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1751947|<?php
//+your+connection
mysql_connect("localhost","root","***");
mysql_select_db("db1");

//+convert+code
$res+=+mysql_query("SHOW+TABLES");
while+($row+=+mysql_fetch_array($res))
{
++++foreach+($row+as+$key+=>+$table)
++++{
++++++++mysql_query("ALTER+TABLE+"+.+$table+.+"+CONVERT+TO+CHARACTER+SET+utf8+COLLATE+utf8_unicode_ci");
++++++++echo+$key+.+"+=&gt;+"+.+$table+.+"+CONVERTED<br+/>";
++++}
}
?>+|code-block|syntax|javascript|1751948|entityMap|0|LINK|mutability|MUTABLE|url|http://www.webhostingtalk.com/showthread.php?t=682119^0|R|9|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

This PHP snippet will change the collation on all tables in a db. (It's taken from <a href="http://www.webhostingtalk.com/showthread.php?t=682119" rel="noreferrer">this site</a>.)

<pre><code>&lt;?php
// your connection
mysql_connect("localhost","root","***");
mysql_select_db("db1");

// convert code
$res = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_array($res))
{
 foreach ($row as $key =&gt; $table)
 {
 mysql_query("ALTER TABLE " . $table . " CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
 echo $key . " =&amp;gt; " . $table . " CONVERTED&lt;br /&gt;";
 }
}
?&gt; 
</code></pre>

blocks|key|1751991|text|字符集和排序规则不是一回事。排序规则是关于如何对字符串进行排序的一组规则。字符集是关于如何表示字符的一组规则。排序规则取决于字符集。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1751992|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Charset and collation are not the same thing. A collation is a set of rules about how to sort strings. A charset is a set of rules about how to represent characters. A collation depends on the charset.

blocks|key|1752031|text|可以在一个命令中完成(而不是PHP的148个命令)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1752032|mysql+--database=dbname+-B+-N+-e+"SHOW+TABLES"+\
%7C+awk+'{print+"SET+foreign_key_checks+=+0;+ALTER+TABLE",+$1,+"CONVERT+TO+CHARACTER+SET+utf8+COLLATE+utf8_general_ci;+SET+foreign_key_checks+=+1;+"}'+\
%7C+mysql+--database=dbname+&|code-block|syntax|javascript|1752033|你一定会喜欢命令行..。(您可能需要对mysql使用--user和--password选项)。|offset|length|style|CODE|1752034|编辑:为了避免外键问题，增加了SET+foreign_key_checks+=+0;和SET+foreign_key_checks+=+1;|1752035|entityMap^0|0|0|J|5|Q|6|X|A|0|F|R|17|R|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]|$I|V|J|W|K|L]|$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@$I|10|J|11|K|L]|$I|12|J|13|K|L]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$]]

Can be done in a single command (rather than 148 of PHP):

<pre><code>mysql --database=dbname -B -N -e "SHOW TABLES" \
| awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' \
| mysql --database=dbname &amp;
</code></pre>

You've got to love the commandline...
(You might need to employ the <code>--user</code> and <code>--password</code> options for <code>mysql</code>).

EDIT: to avoid foreign key problems, added <code>SET foreign_key_checks = 0;</code> and <code>SET foreign_key_checks = 1;</code>

blocks|key|1537140|text|上面脚本的更完整版本可以在这里找到：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1537141|http://www.zen-cart.com/index.php?main_page=product_contrib_info&products_id=1937|offset|length|1537142|请在此处留下对此贡献的任何反馈：http://www.zen-cart.com/forum/showthread.php?p=1034214|1537143|entityMap|0|LINK|mutability|MUTABLE|url|1|http://www.zen-cart.com/forum/showthread.php?p=1034214^0|0|0|29|0|0|G|1I|1|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@$D|S|E|T|1|U]]|A|$]]|$1|F|3|G|5|6|7|V|8|@]|9|@$D|W|E|X|1|Y]]|A|$]]|$1|H|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]|O|$5|K|L|M|A|$N|P]]]]

A more complete version of the script above can be found here:

<a href="http://www.zen-cart.com/index.php?main_page=product_contrib_info&amp;products_id=1937" rel="nofollow">http://www.zen-cart.com/index.php?main_page=product_contrib_info&amp;products_id=1937</a>

Please leave any feedback about this contribution here:<a href="http://www.zen-cart.com/forum/showthread.php?p=1034214" rel="nofollow">http://www.zen-cart.com/forum/showthread.php?p=1034214</a>

blocks|key|1537177|text|在上面的脚本中，您可以选择所有要转换的表(使用SHOW+TABLES)，但更方便和可移植的方式是在转换表之前检查表的排序规则。此查询完成此操作：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1537178|SELECT+table_name
+++++,+table_collation+
FROM+information_schema.tables|code-block|syntax|javascript|1537179|entityMap^0|N|B|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

In scripts above all tables selected for convertation (with <code>SHOW TABLES</code>), but a more convenient and portable way to check the table collation before converting a table. This query does it:

<pre class="lang-sql prettyprint-override"><code>SELECT table_name
 , table_collation 
FROM information_schema.tables
</code></pre>

blocks|key|1537227|text|另一种使用命令行的方法，基于@david的，没有awk|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1537228|for+t+in+$(mysql+--user=root+--password=admin++--database=DBNAME+-e+"show+tables";);do+echo+"Altering"+$t;mysql+--user=root+--password=admin+--database=DBNAME+-e+"ALTER+TABLE+$t+CONVERT+TO+CHARACTER+SET+utf8+COLLATE+utf8_unicode_ci;";done|code-block|syntax|javascript|1537229|美化|1537230|++for+t+in+$(mysql+--user=root+--password=admin++--database=DBNAME+-e+"show+tables";);
++++do+
+++++++echo+"Altering"+$t;
+++++++mysql+--user=root+--password=admin+--database=DBNAME+-e+"ALTER+TABLE+$t+CONVERT+TO+CHARACTER+SET+utf8+COLLATE+utf8_unicode_ci;";
++++done|1537231|entityMap^0|O|3|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

Another approach using command line, based on @david's without the <code>awk</code>

<pre><code>for t in $(mysql --user=root --password=admin --database=DBNAME -e "show tables";);do echo "Altering" $t;mysql --user=root --password=admin --database=DBNAME -e "ALTER TABLE $t CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;";done
</code></pre>

prettified

<pre><code> for t in $(mysql --user=root --password=admin --database=DBNAME -e "show tables";);
 do 
 echo "Altering" $t;
 mysql --user=root --password=admin --database=DBNAME -e "ALTER TABLE $t CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;";
 done
</code></pre>

blocks|key|1752219|text|使用我的自定义外壳collatedb，它应该可以工作：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1752220|collatedb+<username>+<password>+<database>+<collation>|code-block|syntax|javascript|1752221|示例：|1752222|collatedb+root+0000+myDatabase+utf8_bin|1752223|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/abdennour/miscs.sh/blob/master/collatedb^0|9|9|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|C|$G|H]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

Use my custom shell <a href="https://github.com/abdennour/miscs.sh/blob/master/collatedb" rel="nofollow">collatedb</a>, it should work : 

<pre><code>collatedb &lt;username&gt; &lt;password&gt; &lt;database&gt; &lt;collation&gt;
</code></pre>

Example :

<pre><code>collatedb root 0000 myDatabase utf8_bin
</code></pre>

blocks|key|1752290|text|感谢@nlaq的代码，它让我开始了下面的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1752291|我发布了一个WordPress插件，但没有意识到WordPress不会自动设置排序。所以很多使用这个插件的人在本应该是utf8_general_ci的时候却选择了latin1_swedish_ci。|offset|length|style|CODE|1752292|下面是我添加到插件中的代码，用于检测latin1_swedish_ci排序并将其更改为utf8_general_ci。|1752293|在你自己的插件中使用之前，测试一下这段代码！|1752294|//+list+the+names+of+your+wordpress+plugin+database+tables+(without+db+prefix)
$tables_to_check+=+array(
++++'social_message',
++++'social_facebook',
++++'social_facebook_message',
++++'social_facebook_page',
++++'social_google',
++++'social_google_mesage',
++++'social_twitter',
++++'social_twitter_message',
);
//+choose+the+collate+to+search+for+and+replace:
$convert_fields_collate_from+=+'latin1_swedish_ci';
$convert_fields_collate_to+=+'utf8_general_ci';
$convert_tables_character_set_to+=+'utf8';
$show_debug_messages+=+false;
global+$wpdb;
$wpdb->show_errors();
foreach($tables_to_check+as+$table)+{
++++$table+=+$wpdb->prefix+.+$table;
++++$indicies+=+$wpdb->get_results(++"SHOW+INDEX+FROM+`$table`",+ARRAY_A+);
++++$results+=+$wpdb->get_results(+"SHOW+FULL+COLUMNS+FROM+`$table`"+,+ARRAY_A+);
++++foreach($results+as+$result){
++++++++if($show_debug_messages)echo+"Checking+field+".$result['Field']+."+with+collat:+".$result['Collation']."\n";
++++++++if(isset($result['Field'])+&&+$result['Field']+&&+isset($result['Collation'])+&&+$result['Collation']+==+$convert_fields_collate_from){
++++++++++++if($show_debug_messages)echo+"Table:+$table+-+Converting+field+"+.$result['Field']+."+-+"+.$result['Type']."+-+from+$convert_fields_collate_from+to+$convert_fields_collate_to+\n";
++++++++++++//+found+a+field+to+convert.+check+if+there's+an+index+on+this+field.
++++++++++++//+we+have+to+remove+index+before+converting+field+to+binary.
++++++++++++$is_there_an_index+=+false;
++++++++++++foreach($indicies+as+$index){
++++++++++++++++if+(+isset($index['Column_name'])+&&+$index['Column_name']+==+$result['Field']){
++++++++++++++++++++//+there's+an+index+on+this+column!+store+it+for+adding+later+on.
++++++++++++++++++++$is_there_an_index+=+$index;
++++++++++++++++++++$wpdb->query(+$wpdb->prepare(+"ALTER+TABLE+`%25s`+DROP+INDEX+%25s",+$table,+$index['Key_name'])+);
++++++++++++++++++++if($show_debug_messages)echo+"Dropped+index+".$index['Key_name']."+before+converting+field..+\n";
++++++++++++++++++++break;
++++++++++++++++}
++++++++++++}
++++++++++++$set+=+false;

++++++++++++if+(+preg_match(+"/%5Evarchar$(\d%2B)$$/i",+$result['Type'],+$mat+)+)+{
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`{$table}`+MODIFY+`{$result['Field']}`+VARBINARY({$mat[1]})"+);
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`{$table}`+MODIFY+`{$result['Field']}`+VARCHAR({$mat[1]})+CHARACTER+SET+{$convert_tables_character_set_to}+COLLATE+{$convert_fields_collate_to}"+);
++++++++++++++++$set+=+true;
++++++++++++}+else+if+(+!strcasecmp(+$result['Type'],+"CHAR"+)+)+{
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`{$table}`+MODIFY+`{$result['Field']}`+BINARY(1)"+);
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`{$table}`+MODIFY+`{$result['Field']}`+VARCHAR(1)+CHARACTER+SET+{$convert_tables_character_set_to}+COLLATE+{$convert_fields_collate_to}"+);
++++++++++++++++$set+=+true;
++++++++++++}+else+if+(+!strcasecmp(+$result['Type'],+"TINYTEXT"+)+)+{
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`{$table}`+MODIFY+`{$result['Field']}`+TINYBLOB"+);
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`{$table}`+MODIFY+`{$result['Field']}`+TINYTEXT+CHARACTER+SET+{$convert_tables_character_set_to}+COLLATE+{$convert_fields_collate_to}"+);
++++++++++++++++$set+=+true;
++++++++++++}+else+if+(+!strcasecmp(+$result['Type'],+"MEDIUMTEXT"+)+)+{
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`{$table}`+MODIFY+`{$result['Field']}`+MEDIUMBLOB"+);
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`{$table}`+MODIFY+`{$result['Field']}`+MEDIUMTEXT+CHARACTER+SET+{$convert_tables_character_set_to}+COLLATE+{$convert_fields_collate_to}"+);
++++++++++++++++$set+=+true;
++++++++++++}+else+if+(+!strcasecmp(+$result['Type'],+"LONGTEXT"+)+)+{
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`{$table}`+MODIFY+`{$result['Field']}`+LONGBLOB"+);
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`{$table}`+MODIFY+`{$result['Field']}`+LONGTEXT+CHARACTER+SET+{$convert_tables_character_set_to}+COLLATE+{$convert_fields_collate_to}"+);
++++++++++++++++$set+=+true;
++++++++++++}+else+if+(+!strcasecmp(+$result['Type'],+"TEXT"+)+)+{
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`{$table}`+MODIFY+`{$result['Field']}`+BLOB"+);
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`{$table}`+MODIFY+`{$result['Field']}`+TEXT+CHARACTER+SET+{$convert_tables_character_set_to}+COLLATE+{$convert_fields_collate_to}"+);
++++++++++++++++$set+=+true;
++++++++++++}else{
++++++++++++++++if($show_debug_messages)echo+"Failed+to+change+field+-+unsupported+type:+".$result['Type']."\n";
++++++++++++}
++++++++++++if($set){
++++++++++++++++if($show_debug_messages)echo+"Altered+field+success!+\n";
++++++++++++++++$wpdb->query(+"ALTER+TABLE+`$table`+MODIFY+{$result['Field']}+COLLATE+$convert_fields_collate_to"+);
++++++++++++}
++++++++++++if($is_there_an_index+!==+false){
++++++++++++++++//+add+the+index+back.
++++++++++++++++if+(+!$is_there_an_index["Non_unique"]+)+{
++++++++++++++++++++$wpdb->query(+"CREATE+UNIQUE+INDEX+`{$is_there_an_index['Key_name']}`+ON+`{$table}`+({$is_there_an_index['Column_name']})",+$is_there_an_index['Key_name'],+$table,+$is_there_an_index['Column_name']+);
++++++++++++++++}+else+{
++++++++++++++++++++$wpdb->query(+"CREATE+UNIQUE+INDEX+`{$is_there_an_index['Key_name']}`+ON+`{$table}`+({$is_there_an_index['Column_name']})",+$is_there_an_index['Key_name'],+$table,+$is_there_an_index['Column_name']+);
++++++++++++++++}
++++++++++++}
++++++++}
++++}
++++//+set+default+collate
++++$wpdb->query(+"ALTER+TABLE+`{$table}`+DEFAULT+CHARACTER+SET+{$convert_tables_character_set_to}+COLLATE+{$convert_fields_collate_to}"+);
++++if($show_debug_messages)echo+"Finished+with+table+$table+\n";
}
$wpdb->hide_errors();|code-block|syntax|javascript|1752295|entityMap^0|0|1N|F|29|H|0|I|H|17|F|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]|$D|W|E|X|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Y|8|@$D|Z|E|10|F|G]|$D|11|E|12|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|13|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|14|8|@]|9|@]|A|$O|P]]|$1|Q|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|R|$]]

Thanks @nlaq for the code, that got me started on the below solution.

I released a WordPress plugin without realising that WordPress doesn't set the collate automatically. So a lot of people using the plugin ended up with <code>latin1_swedish_ci</code> when it should have been <code>utf8_general_ci</code>.

Here's the code I added to the plugin to detect the <code>latin1_swedish_ci</code> collate and change it to <code>utf8_general_ci</code>.

Test this code before using it in your own plugin!

<pre><code>// list the names of your wordpress plugin database tables (without db prefix)
$tables_to_check = array(
 'social_message',
 'social_facebook',
 'social_facebook_message',
 'social_facebook_page',
 'social_google',
 'social_google_mesage',
 'social_twitter',
 'social_twitter_message',
);
// choose the collate to search for and replace:
$convert_fields_collate_from = 'latin1_swedish_ci';
$convert_fields_collate_to = 'utf8_general_ci';
$convert_tables_character_set_to = 'utf8';
$show_debug_messages = false;
global $wpdb;
$wpdb-&gt;show_errors();
foreach($tables_to_check as $table) {
 $table = $wpdb-&gt;prefix . $table;
 $indicies = $wpdb-&gt;get_results( "SHOW INDEX FROM `$table`", ARRAY_A );
 $results = $wpdb-&gt;get_results( "SHOW FULL COLUMNS FROM `$table`" , ARRAY_A );
 foreach($results as $result){
 if($show_debug_messages)echo "Checking field ".$result['Field'] ." with collat: ".$result['Collation']."\n";
 if(isset($result['Field']) &amp;&amp; $result['Field'] &amp;&amp; isset($result['Collation']) &amp;&amp; $result['Collation'] == $convert_fields_collate_from){
 if($show_debug_messages)echo "Table: $table - Converting field " .$result['Field'] ." - " .$result['Type']." - from $convert_fields_collate_from to $convert_fields_collate_to \n";
 // found a field to convert. check if there's an index on this field.
 // we have to remove index before converting field to binary.
 $is_there_an_index = false;
 foreach($indicies as $index){
 if ( isset($index['Column_name']) &amp;&amp; $index['Column_name'] == $result['Field']){
 // there's an index on this column! store it for adding later on.
 $is_there_an_index = $index;
 $wpdb-&gt;query( $wpdb-&gt;prepare( "ALTER TABLE `%s` DROP INDEX %s", $table, $index['Key_name']) );
 if($show_debug_messages)echo "Dropped index ".$index['Key_name']." before converting field.. \n";
 break;
 }
 }
 $set = false;

 if ( preg_match( "/^varchar$(\d+)$$/i", $result['Type'], $mat ) ) {
 $wpdb-&gt;query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` VARBINARY({$mat[1]})" );
 $wpdb-&gt;query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` VARCHAR({$mat[1]}) CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
 $set = true;
 } else if ( !strcasecmp( $result['Type'], "CHAR" ) ) {
 $wpdb-&gt;query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` BINARY(1)" );
 $wpdb-&gt;query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` VARCHAR(1) CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
 $set = true;
 } else if ( !strcasecmp( $result['Type'], "TINYTEXT" ) ) {
 $wpdb-&gt;query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` TINYBLOB" );
 $wpdb-&gt;query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` TINYTEXT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
 $set = true;
 } else if ( !strcasecmp( $result['Type'], "MEDIUMTEXT" ) ) {
 $wpdb-&gt;query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` MEDIUMBLOB" );
 $wpdb-&gt;query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` MEDIUMTEXT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
 $set = true;
 } else if ( !strcasecmp( $result['Type'], "LONGTEXT" ) ) {
 $wpdb-&gt;query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` LONGBLOB" );
 $wpdb-&gt;query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` LONGTEXT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
 $set = true;
 } else if ( !strcasecmp( $result['Type'], "TEXT" ) ) {
 $wpdb-&gt;query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` BLOB" );
 $wpdb-&gt;query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` TEXT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
 $set = true;
 }else{
 if($show_debug_messages)echo "Failed to change field - unsupported type: ".$result['Type']."\n";
 }
 if($set){
 if($show_debug_messages)echo "Altered field success! \n";
 $wpdb-&gt;query( "ALTER TABLE `$table` MODIFY {$result['Field']} COLLATE $convert_fields_collate_to" );
 }
 if($is_there_an_index !== false){
 // add the index back.
 if ( !$is_there_an_index["Non_unique"] ) {
 $wpdb-&gt;query( "CREATE UNIQUE INDEX `{$is_there_an_index['Key_name']}` ON `{$table}` ({$is_there_an_index['Column_name']})", $is_there_an_index['Key_name'], $table, $is_there_an_index['Column_name'] );
 } else {
 $wpdb-&gt;query( "CREATE UNIQUE INDEX `{$is_there_an_index['Key_name']}` ON `{$table}` ({$is_there_an_index['Column_name']})", $is_there_an_index['Key_name'], $table, $is_there_an_index['Column_name'] );
 }
 }
 }
 }
 // set default collate
 $wpdb-&gt;query( "ALTER TABLE `{$table}` DEFAULT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
 if($show_debug_messages)echo "Finished with table $table \n";
}
$wpdb-&gt;hide_errors();
</code></pre>

blocks|key|1537393|text|我更新了nlaq的答案，以便与PHP7一起工作，并正确处理多列索引、二进制整理数据(例如latin1_bin)等，并对代码进行了一些清理。这是我找到/尝试的唯一一段成功地将我的数据库从latin1迁移到utf8的代码。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1537394|<?php

///////////+BEGIN+CONFIG+////////////////////

$username+=+"";
$password+=+"";
$db+=+"";
$host+=+"";

$target_charset+=+"utf8";
$target_collation+=+"utf8_unicode_ci";
$target_bin_collation+=+"utf8_bin";

///////////++END+CONFIG++////////////////////

function+MySQLSafeQuery($conn,+$query)+{
++++$res+=+mysqli_query($conn,+$query);
++++if+(mysqli_errno($conn))+{
++++++++echo+"Mysql+Error:+"+.+mysqli_error($conn)+.+"\n";
++++++++echo+"This+query+caused+the+above+error:+"+.+$query+.+"\n";
++++}
++++return+$res;
}

function+binary_typename($type)+{
++++$mysql_type_to_binary_type_map+=+array(
++++++++"VARCHAR"+=>+"VARBINARY",
++++++++"CHAR"+=>+"BINARY(1)",
++++++++"TINYTEXT"+=>+"TINYBLOB",
++++++++"MEDIUMTEXT"+=>+"MEDIUMBLOB",
++++++++"LONGTEXT"+=>+"LONGBLOB",
++++++++"TEXT"+=>+"BLOB"
++++);

++++$typename+=+"";
++++if+(preg_match("/%5Evarchar$(\d%2B)$$/i",+$type,+$mat))
++++++++$typename+=+$mysql_type_to_binary_type_map["VARCHAR"]+.+"("+.+(2*$mat[1])+.+")";
++++else+if+(!strcasecmp($type,+"CHAR"))
++++++++$typename+=+$mysql_type_to_binary_type_map["CHAR"]+.+"(1)";
++++else+if+(array_key_exists(strtoupper($type),+$mysql_type_to_binary_type_map))
++++++++$typename+=+$mysql_type_to_binary_type_map[strtoupper($type)];
++++return+$typename;
}

echo+"<pre>";

//+Connect+to+database
$conn+=+mysqli_connect($host,+$username,+$password);
mysqli_select_db($conn,+$db);

//+Get+list+of+tables
$tabs+=+array();
$query+=+"SHOW+TABLES";
$res+=+MySQLSafeQuery($conn,+$query);
while+(($row+=+mysqli_fetch_row($res))+!=+null)
++++$tabs[]+=+$row[0];

//+Now+fix+tables
foreach+($tabs+as+$tab)+{
++++$res+=+MySQLSafeQuery($conn,+"SHOW+INDEX+FROM+`{$tab}`");
++++$indicies+=+array();

++++while+(($row+=+mysqli_fetch_array($res))+!=+null)+{
++++++++if+($row[2]+!=+"PRIMARY")+{
++++++++++++$append+=+true;
++++++++++++foreach+($indicies+as+$index)+{
++++++++++++++++if+($index["name"]+==+$row[2])+{
++++++++++++++++++++$index["col"][]+=+$row[4];
++++++++++++++++++++$append+=+false;
++++++++++++++++}
++++++++++++}
++++++++++++if($append)
++++++++++++++++$indicies[]+=+array("name"+=>+$row[2],+"unique"+=>+!($row[1]+==+"1"),+"col"+=>+array($row[4]));
++++++++}
++++}

++++foreach+($indicies+as+$index)+{
++++++++MySQLSafeQuery($conn,+"ALTER+TABLE+`{$tab}`+DROP+INDEX+`{$index["name"]}`");
++++++++echo+"Dropped+index+{$index["name"]}.+Unique:+{$index["unique"]}\n";
++++}

++++$res+=+MySQLSafeQuery($conn,+"SHOW+FULL+COLUMNS+FROM+`{$tab}`");
++++while+(($row+=+mysqli_fetch_array($res))+!=+null)+{
++++++++$name+=+$row[0];
++++++++$type+=+$row[1];
++++++++$current_collation+=+$row[2];
++++++++$target_collation_bak+=+$target_collation;
++++++++if(!strcasecmp($current_collation,+"latin1_bin"))
++++++++++++$target_collation+=+$target_bin_collation;
++++++++$set+=+false;
++++++++$binary_typename+=+binary_typename($type);
++++++++if+($binary_typename+!=+"")+{
++++++++++++MySQLSafeQuery($conn,+"ALTER+TABLE+`{$tab}`+MODIFY+`{$name}`+{$binary_typename}");
++++++++++++MySQLSafeQuery($conn,+"ALTER+TABLE+`{$tab}`+MODIFY+`{$name}`+{$type}+CHARACTER+SET+'{$target_charset}'+COLLATE+'{$target_collation}'");
++++++++++++$set+=+true;
++++++++++++echo+"Altered+field+{$name}+on+{$tab}+from+type+{$type}\n";
++++++++}
++++++++$target_collation+=+$target_collation_bak;
++++}

++++//+Rebuild+indicies
++++foreach+($indicies+as+$index)+{
+++++++++//+Handle+multi-column+indices
+++++++++$joined_col_str+=+"";
+++++++++foreach+($index["col"]+as+$col)
+++++++++++++$joined_col_str+=+$joined_col_str+.+",+`"+.+$col+.+"`";
+++++++++$joined_col_str+=+substr($joined_col_str,+2);

+++++++++$query+=+"";
+++++++++if+($index["unique"])
+++++++++++++$query+=+"CREATE+UNIQUE+INDEX+`{$index["name"]}`+ON+`{$tab}`+({$joined_col_str})";
+++++++++else
+++++++++++++$query+=+"CREATE+INDEX+`{$index["name"]}`+ON+`{$tab}`+({$joined_col_str})";
+++++++++MySQLSafeQuery($conn,+$query);

++++++++echo+"Created+index+{$index["name"]}+on+{$tab}.+Unique:+{$index["unique"]}\n";
++++}

++++//+Set+default+character+set+and+collation+for+table
++++MySQLSafeQuery($conn,+"ALTER+TABLE+`{$tab}`++DEFAULT+CHARACTER+SET+'{$target_charset}'+COLLATE+'{$target_collation}'");
}

//+Set+default+character+set+and+collation+for+database
MySQLSafeQuery($conn,+"ALTER+DATABASE+`{$db}`+DEFAULT+CHARACTER+SET+'{$target_charset}'+COLLATE+'{$target_collation}'");

mysqli_close($conn);
echo+"</pre>";

?>|code-block|syntax|javascript|1537395|entityMap^0|18|A|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

I updated nlaq's answer to work with PHP7 and to correctly handle multicolumn indices, binary collated data (e.g. <code>latin1_bin</code>), etc., and cleaned up the code a bit. This is the only code I found/tried that successfully migrated my database from latin1 to utf8.

<pre class="lang-php prettyprint-override"><code>&lt;?php

/////////// BEGIN CONFIG ////////////////////

$username = "";
$password = "";
$db = "";
$host = "";

$target_charset = "utf8";
$target_collation = "utf8_unicode_ci";
$target_bin_collation = "utf8_bin";

/////////// END CONFIG ////////////////////

function MySQLSafeQuery($conn, $query) {
 $res = mysqli_query($conn, $query);
 if (mysqli_errno($conn)) {
 echo "&lt;b&gt;Mysql Error: " . mysqli_error($conn) . "&lt;/b&gt;\n";
 echo "&lt;span&gt;This query caused the above error: &lt;i&gt;" . $query . "&lt;/i&gt;&lt;/span&gt;\n";
 }
 return $res;
}

function binary_typename($type) {
 $mysql_type_to_binary_type_map = array(
 "VARCHAR" =&gt; "VARBINARY",
 "CHAR" =&gt; "BINARY(1)",
 "TINYTEXT" =&gt; "TINYBLOB",
 "MEDIUMTEXT" =&gt; "MEDIUMBLOB",
 "LONGTEXT" =&gt; "LONGBLOB",
 "TEXT" =&gt; "BLOB"
 );

 $typename = "";
 if (preg_match("/^varchar$(\d+)$$/i", $type, $mat))
 $typename = $mysql_type_to_binary_type_map["VARCHAR"] . "(" . (2*$mat[1]) . ")";
 else if (!strcasecmp($type, "CHAR"))
 $typename = $mysql_type_to_binary_type_map["CHAR"] . "(1)";
 else if (array_key_exists(strtoupper($type), $mysql_type_to_binary_type_map))
 $typename = $mysql_type_to_binary_type_map[strtoupper($type)];
 return $typename;
}

echo "&lt;pre&gt;";

// Connect to database
$conn = mysqli_connect($host, $username, $password);
mysqli_select_db($conn, $db);

// Get list of tables
$tabs = array();
$query = "SHOW TABLES";
$res = MySQLSafeQuery($conn, $query);
while (($row = mysqli_fetch_row($res)) != null)
 $tabs[] = $row[0];

// Now fix tables
foreach ($tabs as $tab) {
 $res = MySQLSafeQuery($conn, "SHOW INDEX FROM `{$tab}`");
 $indicies = array();

 while (($row = mysqli_fetch_array($res)) != null) {
 if ($row[2] != "PRIMARY") {
 $append = true;
 foreach ($indicies as $index) {
 if ($index["name"] == $row[2]) {
 $index["col"][] = $row[4];
 $append = false;
 }
 }
 if($append)
 $indicies[] = array("name" =&gt; $row[2], "unique" =&gt; !($row[1] == "1"), "col" =&gt; array($row[4]));
 }
 }

 foreach ($indicies as $index) {
 MySQLSafeQuery($conn, "ALTER TABLE `{$tab}` DROP INDEX `{$index["name"]}`");
 echo "Dropped index {$index["name"]}. Unique: {$index["unique"]}\n";
 }

 $res = MySQLSafeQuery($conn, "SHOW FULL COLUMNS FROM `{$tab}`");
 while (($row = mysqli_fetch_array($res)) != null) {
 $name = $row[0];
 $type = $row[1];
 $current_collation = $row[2];
 $target_collation_bak = $target_collation;
 if(!strcasecmp($current_collation, "latin1_bin"))
 $target_collation = $target_bin_collation;
 $set = false;
 $binary_typename = binary_typename($type);
 if ($binary_typename != "") {
 MySQLSafeQuery($conn, "ALTER TABLE `{$tab}` MODIFY `{$name}` {$binary_typename}");
 MySQLSafeQuery($conn, "ALTER TABLE `{$tab}` MODIFY `{$name}` {$type} CHARACTER SET '{$target_charset}' COLLATE '{$target_collation}'");
 $set = true;
 echo "Altered field {$name} on {$tab} from type {$type}\n";
 }
 $target_collation = $target_collation_bak;
 }

 // Rebuild indicies
 foreach ($indicies as $index) {
 // Handle multi-column indices
 $joined_col_str = "";
 foreach ($index["col"] as $col)
 $joined_col_str = $joined_col_str . ", `" . $col . "`";
 $joined_col_str = substr($joined_col_str, 2);

 $query = "";
 if ($index["unique"])
 $query = "CREATE UNIQUE INDEX `{$index["name"]}` ON `{$tab}` ({$joined_col_str})";
 else
 $query = "CREATE INDEX `{$index["name"]}` ON `{$tab}` ({$joined_col_str})";
 MySQLSafeQuery($conn, $query);

 echo "Created index {$index["name"]} on {$tab}. Unique: {$index["unique"]}\n";
 }

 // Set default character set and collation for table
 MySQLSafeQuery($conn, "ALTER TABLE `{$tab}` DEFAULT CHARACTER SET '{$target_charset}' COLLATE '{$target_collation}'");
}

// Set default character set and collation for database
MySQLSafeQuery($conn, "ALTER DATABASE `{$db}` DEFAULT CHARACTER SET '{$target_charset}' COLLATE '{$target_collation}'");

mysqli_close($conn);
echo "&lt;/pre&gt;";

?&gt;
</code></pre>

blocks|key|1537462|text|无论如何，这里有一个作为Yii2+ConsoleController类的https://stackoverflow.com/a/42545503/6226915的扩展版本，它也可以独立使用：https://gist.github.com/cboulanger/d30c197235a53d9a2331f19a96d6e00d。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1537463|该脚本修复了一些错误，并添加了对全文索引的支持；它还处理3kb的常规索引键长度约束。它还将所有表转换为InnoDB。|1537464|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/42545503/6226915|1|https://gist.github.com/cboulanger/d30c197235a53d9a2331f19a96d6e00d^0|10|18|0|2N|1V|1|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]|$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]]]

For what it's worth, here's an extended version of <a href="https://stackoverflow.com/a/42545503/6226915">https://stackoverflow.com/a/42545503/6226915</a> as a Yii2 ConsoleController class, which can also be used standalone: <a href="https://gist.github.com/cboulanger/d30c197235a53d9a2331f19a96d6e00d" rel="nofollow noreferrer">https://gist.github.com/cboulanger/d30c197235a53d9a2331f19a96d6e00d</a> .
The script fixes a few bugs and adds support for fulltext indexes; also it deals with the regular index key length constraint of 3kb. It also converts all tables to InnoDB.

Is there a <code>SQL</code> or <code>PHP</code> 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.

A script to change all tables and fields to the utf-8-bin collation in MYSQL

数据库

WordPress

是否有可以运行的SQL或PHP脚本来更改数据库中所有表和字段的默认排序规则？我可以自己写一个，但我认为这应该是在像这样的网站上容易获得的东西。如果我能在别人发布之前自己想出一个，我会自己发布的。

问在MYSQL中将所有表和字段更改为utf-8-bin归类的脚本
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MYSQL中将所有表和字段更改为utf-8-bin归类的脚本EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MYSQL中将所有表和字段更改为utf-8-bin归类的脚本
EN