1.statusコマンドを実行する
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> status -------------- mysql Ver 14.14 Distrib 5.5.25, for Linux (i686) using readline 5.1 Connection id: 424 Current database: symfony3 Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.51a MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 11 days 23 hours 38 min 26 sec Threads: 1 Questions: 107197 Slow queries: 0 Opens: 19 Flush tables: 1 Open tables: 13 Queries per second avg: 0.104 -------------- |
2. show variablesで調べる
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> show variables like 'character_set%'; +--------------------------+----------------------------------------+ | 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/mysql/share/mysql/charsets/ | +--------------------------+----------------------------------------+ 8 rows in set (0.00 sec) |
/
テーブルごとの設定確認
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
mysql> show table status from mysql; +---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+ | 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 | +---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+ | columns_priv | MyISAM | 10 | Fixed | 0 | 0 | 0 | 241505530017742847 | 4096 | 0 | NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL | utf8_bin | NULL | | Column privileges | | db | MyISAM | 10 | Fixed | 25 | 488 | 12200 | 137359788634800127 | 5120 | 0 | NULL | 2018-09-04 11:04:29 | 2019-10-10 23:50:25 | NULL | utf8_bin | NULL | | Database privileges | | engine_cost | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | | | event | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 2048 | 0 | NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL | utf8_general_ci | NULL | | Events | | func | MyISAM | 10 | Fixed | 0 | 0 | 0 | 162974011515469823 | 1024 | 0 | NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL | utf8_bin | NULL | | User defined functions | | general_log | CSV | 10 | Dynamic | 2 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | General log | | gtid_executed | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | latin1_swedish_ci | NULL | | | | help_category | InnoDB | 10 | Dynamic | 40 | 409 | 16384 | 0 | 16384 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | help categories | | help_keyword | InnoDB | 10 | Dynamic | 728 | 135 | 98304 | 0 | 81920 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | help keywords | | help_relation | InnoDB | 10 | Dynamic | 2253 | 36 | 81920 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | keyword-topic relation | | help_topic | InnoDB | 10 | Dynamic | 656 | 2422 | 1589248 | 0 | 81920 | 4194304 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | help topics | | innodb_index_stats | InnoDB | 10 | Dynamic | 513 | 159 | 81920 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | 2020-02-15 17:45:56 | NULL | utf8_bin | NULL | stats_persistent=0 | | | innodb_table_stats | InnoDB | 10 | Dynamic | 34 | 481 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | 2020-02-15 17:45:56 | NULL | utf8_bin | NULL | stats_persistent=0 | | | ndb_binlog_index | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL | latin1_swedish_ci | NULL | | | | plugin | InnoDB | 10 | Dynamic | 1 | 16384 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | MySQL plugins | | proc | MyISAM | 10 | Dynamic | 48 | 6261 | 300528 | 281474976710655 | 4096 | 0 | NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL | utf8_general_ci | NULL | | Stored Procedures | | procs_priv | MyISAM | 10 | Fixed | 0 | 0 | 0 | 266275327968280575 | 4096 | 0 | NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL | utf8_bin | NULL | | Procedure privileges | | proxies_priv | MyISAM | 10 | Fixed | 1 | 837 | 837 | 235594555506819071 | 9216 | 0 | NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL | utf8_bin | NULL | | User proxy privileges | | server_cost | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | | | servers | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | MySQL Foreign Servers table | | slave_master_info | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Master Information | | slave_relay_log_info | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Relay Log Information | | slave_worker_info | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Worker Information | | slow_log | CSV | 10 | Dynamic | 2 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | Slow log | | tables_priv | MyISAM | 10 | Fixed | 2 | 947 | 1894 | 266556802944991231 | 9216 | 0 | NULL | 2018-09-04 11:04:29 | 2018-09-04 11:04:29 | NULL | utf8_bin | NULL | | Table privileges | | time_zone | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zones | | time_zone_leap_second | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Leap seconds information for time zones | | time_zone_name | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zone names | | time_zone_transition | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zone transitions | | time_zone_transition_type | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2018-09-04 11:04:29 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zone transition types | | user | MyISAM | 10 | Dynamic | 17 | 128 | 2188 | 281474976710655 | 4096 | 0 | NULL | 2018-09-04 11:04:29 | 2019-10-10 23:50:25 | NULL | utf8_bin | NULL | | Users and global privileges | +---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+ 31 rows in set (0.00 sec) |
各フィールドの文字コードを確認する
デフォルトの文字コード以外の場合、 show create table <テーブル名> を実行すると、デフォルト以外の文字コードのフィールドには”CHARACTER SET <文字コード>“と表記されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
CREATE TABLE `user` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '', `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '', `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, `max_questions` int(11) unsigned NOT NULL DEFAULT '0', `max_updates` int(11) unsigned NOT NULL DEFAULT '0', `max_connections` int(11) unsigned NOT NULL DEFAULT '0', `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0', `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password', `authentication_string` text COLLATE utf8_bin, `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `password_last_changed` timestamp NULL DEFAULT NULL, `password_lifetime` smallint(5) unsigned DEFAULT NULL, `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' |
文字コードの設定方法
テーブル全体のデフォルト文字コードは以下で設定する。
|
1 |
mysql> alter table <テーブル名> default character set <文字コード>; |
以下は例にになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
mysql> show create table highlights; +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | highlights | CREATE TABLE `highlights` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `attr_name` varchar(200) DEFAULT NULL, `attr_id` int(11) DEFAULT NULL, `title` varchar(200) DEFAULT NULL, `content` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table highlights default character set utf8; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table highlights; +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | highlights | CREATE TABLE `highlights` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `attr_name` varchar(200) CHARACTER SET latin1 DEFAULT NULL, `attr_id` int(11) DEFAULT NULL, `title` varchar(200) CHARACTER SET latin1 DEFAULT NULL, `content` text CHARACTER SET latin1, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
テーブルのデフォルト文字コードを変更しても既存のフィールドの文字コードはそのままです。
以下のようにして各フィールドの文字コードも変更できます。
|
1 2 3 |
mysql> alter table highlights modify attr_name varchar(100) character set utf8; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 |
