データベース
確認
データベースの文字コード設定と照合順序設定の確認方法。いずれも対象のデータベースをUSEで使っている状態で実行。
文字コード
SHOW VARIABLES LIKE 'char%';
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
root@localhost [(none)]> 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/local/share/mysql/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.01 sec) |
照合順序
SHOW VARIABLES lIKE 'col%';
|
1 2 3 4 5 6 7 8 9 |
root@localhost [(none)]> SHOW VARIABLES LIKE 'col%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.01 sec) |
変数で一度に確認
SELECT @@character_set_database, @@collation_database;
|
1 2 3 4 5 6 7 |
root@localhost [test]> SELECT @@character_set_database, @@collation_database; +--------------------------+----------------------+ | @@character_set_database | @@collation_database | +--------------------------+----------------------+ | utf8 | utf8_general_ci | +--------------------------+----------------------+ 1 row in set (0.00 sec) |
変更
ALTER DATABASE test CHARACTER SET 文字コード COLLATE 照合順序;
|
1 2 3 4 5 6 7 8 9 10 |
root@localhost [test]> ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; Query OK, 1 row affected (0.00 sec) root@localhost [test]> SELECT @@character_set_database, @@collation_database; +--------------------------+----------------------+ | @@character_set_database | @@collation_database | +--------------------------+----------------------+ | utf8mb4 | utf8mb4_general_ci | +--------------------------+----------------------+ 1 row in set (0.00 sec) |
テーブル
確認
SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='テーブル名';
|
1 2 3 4 5 6 7 |
oot@localhost [test]> SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'; +------------+-----------------+ | TABLE_NAME | TABLE_COLLATION | +------------+-----------------+ | test_table | utf8_general_ci | +------------+-----------------+ 1 row in set (0.01 sec) |
変更
ALTER TABLE test_table
CONVERT TO CHARACTER SET 文字コード
COLLATE 照合順序;
|
1 2 3 4 5 6 7 8 9 10 11 |
root@localhost [test]> ALTER TABLE test_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 root@localhost [test]> SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'; +------------+--------------------+ | TABLE_NAME | TABLE_COLLATION | +------------+--------------------+ | test_table | utf8mb4_general_ci | +------------+--------------------+ 1 row in set (0.01 sec) |