運用・保守

[運用と保守]第62章-それらをエンコードするmysql文字セット



Chapter 62 Mysql Character Set Encoding Those Things



mysql文字セットに関するもの

  • 実験環境を整える
MariaDB [(none)]> CREATE DATABASE test_db character set utf8 Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test_db | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> use test_db Database changed MariaDB [test_db]> create table tb_books ( -> name varchar(45) not null, -> price double not null, -> bookCount int not null, -> author varchar(45) not null ) default charset = utf8 Query OK, 0 rows affected (0.05 sec) MariaDB [test_db]> show tables +-------------------+ | Tables_in_test_db | +-------------------+ | tb_books | +-------------------+ 1 row in set (0.00 sec) MariaDB [test_db]>
  • データベースの文字セットを表示する
MariaDB [test_db]> show variables like 'character_set_database' +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | utf8 | +------------------------+-------+ 1 row in set (0.00 sec)
  • データテーブルの文字セットを表示する
MariaDB [test_db]> show create table tb_books +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_books | CREATE TABLE `tb_books` ( `name` varchar(45) NOT NULL, `price` double NOT NULL, `bookCount` int(11) NOT NULL, `author` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [test_db]>
  • データベースのエンコード形式を変更します
MariaDB [test_db]> alter database test_db character set gb2312 Query OK, 1 row affected (0.01 sec) MariaDB [test_db]> show variables like 'character_set_database' +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | character_set_database | gb2312 | +------------------------+--------+ 1 row in set (0.00 sec) MariaDB [test_db]>
  • データテーブルの文字セットを変更する
MariaDB [test_db]> show create table tb_books +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_books | CREATE TABLE `tb_books` ( `name` varchar(45) NOT NULL, `price` double NOT NULL, `bookCount` int(11) NOT NULL, `author` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [test_db]> alter table tb_books character set gb2312 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test_db]> show create table tb_books +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_books | CREATE TABLE `tb_books` ( `name` varchar(45) CHARACTER SET utf8 NOT NULL, `price` double NOT NULL, `bookCount` int(11) NOT NULL, `author` varchar(45) CHARACTER SET utf8 NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [test_db]>
  • フィールドのエンコード形式を変更する
MariaDB [test_db]> alter table tb_books change name name varchar(45) character set utf8 not null Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test_db]> show create table tb_books +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_books | CREATE TABLE `tb_books` ( `name` varchar(45) CHARACTER SET utf8 NOT NULL, `price` double NOT NULL, `bookCount` int(11) NOT NULL, `author` varchar(45) CHARACTER SET utf8 NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)