一个用于将MYSQL中的所有表和字段更改为utf-8-bin排序规则的脚本。
这个脚本的目的是将MYSQL中的所有表和字段都改为utf-8-bin格式的排序规则。下面我们来整理一下出现这个问题的原因和解决方法。
原因:
1. 原始数据库的表和字段的排序规则不是utf-8-bin,可能是其他的排序规则。
2. 需要将数据库的排序规则改为utf-8-bin,以适应特定的需求或应用程序。
解决方法:
1. 在脚本中设置目标字符集和排序规则的变量:
$target_charset = "utf8"; $target_collate = "utf8_general_ci";
2. 使用mysql_connect函数连接到数据库:
$conn = mysql_connect($host, $username, $password); mysql_select_db($db, $conn);
3. 获取所有表的名称:
$tabs = array(); $res = mysql_query("SHOW TABLES"); while (($row = mysql_fetch_row($res)) != null) { $tabs[] = $row[0]; }
4. 遍历所有表,修改表的排序规则:
foreach ($tabs as $tab) { mysql_query("ALTER TABLE {$tab} DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}"); }
5. 获取每个表的字段信息,根据字段类型修改字段的排序规则:
while (($row = mysql_fetch_array($res)) != null) { $name = $row[0]; $type = $row[1]; // 根据字段类型修改字段的排序规则 // ... // 修改字段的排序规则 mysql_query("ALTER TABLE {$tab} MODIFY {$name} COLLATE {$target_collate}"); }
6. 重新创建表的索引:
foreach ($indicies as $index) { if ($index["unique"]) { mysql_query("CREATE UNIQUE INDEX {$index["name"]} ON {$tab} ({$index["col"]})"); } else { mysql_query("CREATE INDEX {$index["name"]} ON {$tab} ({$index["col"]})"); } }
7. 修改数据库的排序规则:
mysql_query("ALTER DATABASE {$db} DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
8. 关闭数据库连接:
mysql_close($conn);
这个脚本可以帮助将MYSQL数据库中的所有表和字段的排序规则改为utf-8-bin。需要注意的是,在运行脚本之前,请务必备份数据库以防止数据丢失。
问题的原因是数据库中的表和字段的字符集和校对集(collation)不是utf8-bin。解决方法是运行一个包含两个步骤的脚本。第一步是执行一个查询语句,用于生成一系列用于修改表和字段字符集和校对集的语句。第二步是将这些语句复制到命令行或PhpMyAdmin的SQL选项卡中,以实际修改表和字段的字符集和校对集。
以下是完整的脚本:
-- Step 1: SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;') as stmt FROM `information_schema`.`TABLES` t WHERE 1 AND t.`TABLE_SCHEMA` = 'database_name' ORDER BY 1 -- Step 2: -- 将查询结果复制到命令行或PhpMyAdmin的SQL选项卡中,执行修改操作
注意,这个查询语句不会直接对数据库进行修改,而是生成一系列的修改语句。你需要将这些语句复制到命令行或PhpMyAdmin的SQL选项卡中,才能实际修改表和字段的字符集和校对集。
这个方法非常聪明和巧妙,可以批量修改数据库表和字段的字符集和校对集,特别适用于redmine安装中,默认的校对集是latin1而不是utf8的情况。
这篇文章介绍了如何使用一个包含两个步骤的脚本来将MYSQL中所有表和字段的字符集和校对集修改为utf8-bin。原因是数据库中的表和字段的字符集和校对集不是utf8-bin,解决方法是运行一个查询语句生成一系列修改语句,并将这些语句复制到命令行或PhpMyAdmin的SQL选项卡中执行。这个方法非常聪明和巧妙,可以批量修改数据库表和字段的字符集和校对集,特别适用于redmine安装中。
问题的出现原因:
该问题的出现是由于数据库中的表和字段的字符集和排序规则(collation)不是utf8-bin导致的。这可能会导致字符编码问题,例如在字段值中出现乱码或无法正确处理特殊字符。
解决方法:
下面是一个可以在MYSQL中将所有表和字段更改为utf8-bin排序规则的脚本:
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 &
该脚本通过使用mysql命令行工具和awk命令来执行以下步骤:
1. 获取数据库中的所有表名;
2. 针对每个表,执行一系列命令来更改表的字符集和排序规则为utf8-bin;
3. 最后将更改后的命令传递给mysql命令行工具来执行。
需要注意的是,可能需要使用--user
和--password
选项来指定mysql命令行工具的用户名和密码。
此外,脚本中还添加了SET foreign_key_checks = 0;
和SET foreign_key_checks = 1;
来避免外键约束问题。
然而,需要注意的是,这种方法可能会导致一些问题。例如,如果原来的latin1列是TEXT类型的,那么在转换为utf8-bin后可能会变成MEDIUMTEXT类型,这可能会导致数据的静默丢失或截断。因此,在执行此脚本之前,建议参考相关文档了解更多详细信息。
此外,如果数据库中存在视图,上述脚本将会报错。为了排除视图,可以将"SHOW TABLES"
替换为"SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'"
。需要注意的是,这需要MySQL的版本在5.0.2或更高。
最后,还提供了一些变种的命令行代码,例如添加了用户名和密码参数,或者根据特定条件选择表名。可以根据实际需求进行适当的修改和调整。