MySQL校对问题

Question

在我的公司中，数据库中的表创建得很糟糕。每个表都有不同的排序规则和字符集。

当然，这是非常糟糕的，但是在服务器崩溃之前，这会使查询失去很多性能(而且它甚至不是一个很好的数据库.)。

我想知道是否有任何好的MySQL工具、命令或过程来转换表排序规则和字符集。

只是执行alter和执行转换是制动的特殊字符。是正常还是我做错了什么？

编辑：为例:我有一个带有uft8校对的表格财务和一个拉丁文瑞典语的表格费用。每个表有1000到5000行。以下查询大约需要15秒才能执行：

select ex.* from expense ex
   inner join finance fin on fin.ex_id = ex.id

当表具有相同的排序规则时，使用更大的表执行更复杂的查询的速度要快得多。

编辑2:数据库中的另一个错误:行ids都是varchar(15)，而不是int。

Answer 1

我不建议使用“工具”来解决这个问题。

在你做任何事情之前，会转储你的数据库，以便备份，以防你搞砸了;)

您可以简化字符集和整理两种方法。

方法1:移动数据

• 创建一个全新的数据库，并在所有表中配置正确的字符集和排序规则
• 用INSERT SELECT语句填充新表。 插入newdatabase.table选择*从olddatabase.table中插入

MySQL将自动将数据转换为正确的字符集。

方法2:更改表

如果更改现有表的字符集，则所有现有内容也将被转换。

例如：

旧桌

CREATE TABLE `myWrongCharsetTable` (
  `name` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

为演示准备一些数据

INSERT INTO `myWrongCharsetTable` (`name`) VALUES ( 'I am a latino string' );
INSERT INTO `myWrongCharsetTable` (`name`) VALUES ( 'Mein Name ist Müller' );
INSERT INTO `myWrongCharsetTable` (`name`) VALUES ( 'Mein Name ist Möller' );

SELECT * FROM myWrongCharsetTable INTO outfile '/tmp/mylatinotable.csv';

在UTF-8控制台上，我这样做。

# cat /tmp/mylatinotable.csv
I am a latino string
Mein Name ist M▒ller
Mein Name ist M▒ller

对，奇怪的字符集..。这是显示在utf-8控制台上的拉丁文1。

# cat /tmp/mylatinotable.csv | iconv -f latin1 -t utf-8
I am a latino string
Mein Name ist Müller
Mein Name ist Möller

是的，一切都好

，那么我现在该如何解决这个问题呢？？

ALTER TABLE myWrongCharsetTable
    MODIFY name varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', 
    DEFAULT CHARSET = utf8 COLLATE utf8_unicode_ci;

(就是这样:)

再次写入输出文件

mysql> SELECT * FROM myWrongCharsetTable INTO outfile '/tmp/latinoutf8.csv';
Query OK, 3 rows affected (0.01 sec)

mysql> exit
Bye
dbmaster-001 ~ # cat /tmp/latinoutf8.csv

I am a latino string
Mein Name ist Müller
Mein Name ist Möller

工作了，一切都很好，我们很高兴

编辑：

其实还有另外一种方法

方法3:转储、修改和重新加载数据

如果您对sed和awk很在行，您可以自动完成此操作，或者手动编辑该文件。

# dump the structure, possibly routines and triggers
mysqldump -h yourhost -p -u youruser --no-data --triggers --skip-comments --routines yourdatabase > database_structure_routines.sql

# dump the data
mysqldump -h yourhost -p -u youruser --no-create-info --skip-triggers --skip-routines yourdatabase > database_data.sql

现在，在您选择的编辑器中打开database_structure_routines.sql，并根据需要修改表。

我建议将所有像/*!40101 SET character_set_client = utf8 */这样的注释删除到您的dumpfile中，因为这可能覆盖表的默认值。

完成后，创建一个新的数据库和结构

mysql > CREATE DATABASE `newDatabase` DEFAULT CHARSET utf8 COLLATE utf8_unicode_ci;
mysql > use `newDatabase`
mysql > ./database_structure_routines.sql;

别忘了重新检查你的桌子

mysql > SHOW CREATE TABLE `table`;

如果可以的话，您可以重新导入数据，字符集转换将自动完成

mysql -h yourhost -p -u youruser newDatabase < database_data.sql

希望这能有所帮助

Answer 2

我知道继承那些认为“整理”是某种疾病的人所创建的遗留模式的乐趣。

最好的选择是使用good ole‘mysqldump将带有其数据的表导出到SQL转储文件。然后在转储文件中手动修改create语句，以设置字符集和排序规则。我是“utf8 8”的超级粉丝。如果转储文件很大，可以使用命令行(如sed )来有效地编辑文件，而不必在编辑器中打开它。

然后删除现有表，重新导入修改后的转储。

在我的经验中，你做这件事的任何其他方式都可以是掷骰子。

这也可能是将它们全部转换到同一个存储引擎或将MySQL服务器升级到5.5的好时机。

Answer 3

您可以尝试使用“转换”或“强制转换”来更改字符集--创建一个新列，并使用“强制转换”来使用新更正的字符集填充新列。

http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html