mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'coll%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
mysql> show table status from データベース名 ;
+-------------------------------------+--------+---------+------------+-------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------------------------------+--------+---------+------------+-------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| テーブルB | MyISAM | 10 | Dynamic | 3674 | 39 | 145564 | 281474976710655 | 39936 | 0 | NULL | 2012-05-30 13:20:52 | 2012-05-30 13:20:52 | NULL | utf8_general_ci | NULL | | |
| テーブルA | InnoDB | 10 | Compact | 145 | 451 | 65536 | 0 | 49152 | 45088768 | 172 | 2012-05-28 18:42:27 | NULL | NULL | utf8_unicode_ci | NULL | | |
+-------------------------------------+--------+---------+------------+-------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
56 rows in set (0.02 sec)
あーーーーーーーーーーー。
テーブルB = utf8_general_ci
テーブルA = utf8_unicode_ci
やっと、見えた。
システムAのテーブルはutf8_unicode_ciだけど、システムBはutf8_general_ciだ。
ALTER TABLE `テーブルB` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
mysql> show table status from データベース名 like "テーブルB";
+--------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| テーブルB | MyISAM | 10 | Dynamic | 3674 | 39 | 145564 | 281474976710655 | 39936 | 0 | NULL | 2012-05-30 14:02:45 | 2012-05-30 14:02:45 | NULL | utf8_unicode_ci | NULL | | |
+--------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
修正できたー。
ついでに他のシステムB系テーブルも変更して、統一する。
これで、いけるはず。
mysql> SELECT c.カラムB FROM テーブルA r, テーブルB c WHERE r.カラムA = c.カラムB;
ERROR 1267 (HY000): Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
あれー？
mysql> show table status from データベース名 like "テーブルA";
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| テーブルA | InnoDB | 10 | Compact | 145 | 451 | 65536 | 0 | 49152 | 45088768 | 172 | 2012-05-28 18:42:27 | NULL | NULL | utf8_unicode_ci | NULL | | |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
mysql> show table status from データベース名 like "テーブルB";
+--------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| テーブルB | MyISAM | 10 | Dynamic | 3674 | 39 | 145564 | 281474976710655 | 39936 | 0 | NULL | 2012-05-30 14:02:45 | 2012-05-30 14:02:45 | NULL | utf8_unicode_ci | NULL | | |
+--------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
なんか適用されてないっぽい？
んー、とりあえず、Mysqlの再起動。
# /etc/rc.d/init.d/mysqld restart
mysqld を停止中: [ OK ]
mysqld を起動中: [ OK ]
で、再度。
mysql> SELECT c.カラムB FROM テーブルA r, テーブルB c WHERE r.カラムA = c.カラムB;
ERROR 1267 (HY000): Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
えー・・・・・・・・・
やっぱりdumpするしかないのかー・・・
# mysqldump -u root -p データベース名 > dump_データベース名_20120530.sql
Enter password:
[1]+ 停止 mysqldump -u root -p admin
vi で編集。
カラムBを使用している箇所のみ、
COLLATE utf8_unicode_ci
に変更
これで復元する。
# mysql -u root -p データベース名 < dump_データベース名_20120530.sql
やっと解決しましたー。
以下、参考URL
