冰古blog » wordpress » 2008 » 02 » 06 » latin1_swedish_ci to utf8_general_ci

latin1_swedish_ci to utf8_general_ci

从旧主机搬到新主机最烦人的就是乱码问题。这也是个由来已久的问题了。
wordpress 2.2开始,wordpress提供DB_CHARSET和DB_COLLATE这两个参数以便我们更好地解决这个问题。但似乎这些并不够,仍旧会产生这样那样的问题,如Warning: Invalid argument supplied for foreach() in D:\xampp\htdocs\wordpress\wp-includes\classes.php on line 88。最根本的解决应该还是把数据库里的数据由latin1_swedish_ci格式转换成utf8_general_ci格式。
wordpress官方的论坛就像wordpress那样,很有人气。你遇到的问题,很可能里面已经有人讨论过并已经解决了。乱码问题这个很热门的问题,当然也是完美地解决了地。
以下方法涉及数据库修改,请在备份数据库后操作!
解决的方法是这样的:
1. 下载g30rg3_x提供的这个plugin;
2. 确保你的wordpress是2.2.x或2.1.x,因为这个plugin只支持这两个系列的版本;
3. 上传到plugins文件夹并激活;
4. 到’UTF-8 Database Converter’菜单中按照提示进行剩余操作即可。

上面的办法多了不少的限制,如wordpress的版本需是2.2.x或2.1.x,并能保证你的wordpress还能登录!(是的,很可能你的wordpress这时已经不能登录了。)
这时我们可以借用一下g30rg3_x提供的UTF8_DB_Converter_DoIt()函数:

<?php
define('DB_NAME', 'putyourdbnamehere');    // 数据库名
define('DB_USER', 'usernamehere');     // MySQL用户名
define('DB_PASSWORD', 'yourpasswordhere'); // 密码
define('DB_HOST', 'localhost');    // 很大可能你无需修改此项

function UTF8_DB_Converter_DoIt() {
	$tables = array();
	$tables_with_fields = array();

	// Since we cannot use the WordPress Database Abstraction Class (wp-db.php),
	// we have to make an a stand-alone/direct connection to the database.
	$link_id = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die('Error establishing a database connection');
	mysql_select_db(DB_NAME, $link_id);

	// Gathering information about tables and all the text/string fields that can be affected
	// during the conversion to UTF-8.
	$resource = mysql_query("SHOW TABLES", $link_id);
	while ( $result = mysql_fetch_row($resource) )
		$tables[] = $result[0];

	if ( !empty($tables) ) {
		foreach ( (array) $tables as $table ) {
			$resource = mysql_query("EXPLAIN $table", $link_id);
			while ( $result = mysql_fetch_assoc($resource) ) {
				if ( preg_match('/(char)|(text)|(enum)|(set)/', $result['Type']) )
					$tables_with_fields[$table][$result['Field']] = $result['Type'] . " " . ( "YES" == $result['Null'] ? "" : "NOT " ) . "NULL " .  ( !is_null($result['Default']) ? "DEFAULT '". $result['Default'] ."'" : "" );
			}
		}

		// Change all text/string fields of the tables to their corresponding binary text/string representations.
		foreach ( (array) $tables as $table )
			mysql_query("ALTER TABLE $table CONVERT TO CHARACTER SET binary", $link_id);

		// Change database and tables to UTF-8 Character set.
		mysql_query("ALTER DATABASE " . DB_NAME . " CHARACTER SET utf8", $link_id);
		foreach ( (array) $tables as $table )
			mysql_query("ALTER TABLE $table CONVERT TO CHARACTER SET utf8", $link_id);

		// Return all binary text/string fields previously changed to their original representations.
		foreach ( (array) $tables_with_fields as $table => $fields ) {
			foreach ( (array) $fields as $field_type => $field_options ) {
				mysql_query("ALTER TABLE $table MODIFY $field_type $field_options", $link_id);
			}
		}

		// Optimize tables and finally close the mysql link.
		foreach ( (array) $tables as $table )
			mysql_query("OPTIMIZE TABLE $table", $link_id);
		mysql_close($link_id);
	} else {
		die('<strong>There are no tables?</strong>');
	}

	return true;
}
UTF8_DB_Converter_DoIt();
?>

现在你把上面代码保存到一个php文件中,上传至空间并运行,如无意外你的数据已经完成了latin1_swedish_ci到utf8_general_ci转换。

7 Responses to “latin1_swedish_ci to utf8_general_ci”

  1. […] WordPress以前也有编码问题,这里有个latin1到utf8的转换插件,smf当然是不能用的。不过,从这个插件代码里可以看到,它是如何把数据库表转换为utf8的。 […]

  2. […] 参考资料:latin1_swedish_ci to utf8_general_ci 分类: mysql, php 标签: 评论 (0) Trackbacks (0) 发表评论 Trackback […]

  3. […] This post was mentioned on Twitter by junichi_y, 言巾午凡. 言巾午凡 said: http://bingu.net/472/latin1_swedish_ci-to-utf8_general_ci/ 这个转编码的.php还是比较靠谱的 不过要注意执行时候会不会timeout 和webserver和mysql都有关 […]

  4. […] 原文地址:http://bingu.net/472/latin1_swedish_ci-to-utf8_general_ci/ TAG php TRACKBACK URLhttp://laozhou.tk/blog/wp-trackback.php?p=849 « 小白在出差80多天之后至今生还青岛! No Comment to “简单php将数据库编码latin1_swedish_ci 转 utf8_general_ci” Leave a Reply 点击这里取消回复。 Name (required) Mail (will not be published) (required) Website […]

  5. cuxnil says:

    请教一下,页面里插入的这个文本框是怎么实现的?

  6. 大连王艇 says:

    如果你必须使用outlook等导出的gb2312格式的access数据,你还可以把其中的utf8改为gb2312来使用。当然不是在wp中使用。

  7. g30rg3_x says:

    Hi, thats why i make this plugin open source…
    People can adapt the code to his own needs…

    But i think you should use the under development engine instead of the 2.0.1 engine, cause it will be prepare to handle the fulltext index problems…

    You can check it here
    UTF8_DB_Converter

    Greetings from mexico
    PS: At least one chinese word mmm, your name counts? 😛
    PS: The 1 one chinese word, hi 冰古

Leave a Reply to 数据库乱码的解决 | 搬搬鸟