MySQL 5.7 文字コードをUTF8に!日本語が登録できない!

この記事は約10分で読めます。

MySQL、文字コードの基本・・・
改めて、整理を・・・(笑)

PythonでDB、Insertエラー

# ./xxxx.py 

  File "./xxxx.py", line 72, in db_IN
    cursor.executemany("INSERT INTO xx_master (xx_title,xx_sum) VALUES (%s,%s)", my_xx_list)
  File "/usr/lib64/python3.6/site-packages/MySQLdb/cursors.py", line 234, in executemany
    self._get_db().encoding)
 
MySQLdb._exceptions.OperationalError: (1366, "Incorrect string value: '\\xAA\\xAA\\xAA\\xAA\\xAA\\xAA...' for column 'xx_title' at row 1")

↑「encode」が、どうのこうの、と、いっています・・・

文字コード等でしょうか・・・・

今回、日本語のデータを、Insertしてみましたが、試しに、ローマ字で、Insertしてみます。
すると、うまくいきます。

データベースの文字コードを見てみます。

MySQLデーターベスの文字コード

データベースの文字コードをチェックしてみます。

チェックには、

mysql> use (データベース名)

として、
「show variables like ‘character%’」
というコマンドで、チェックをします。
ysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |←これがまずい!
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |←これがまずい!
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

↑上記、「latin1」となっているのが、原因の一つです。
mysql> set character_set_database=utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)

↑と、SQLコマンドで変更することができます。
mysql>  show variables like 'character%';
+--------------------------+----------------------------+
| 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)

↑と、utf8になり、意図通りになりますが、DBを再起動すると、元に戻ります。
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

↑と、「latin1」のまま、テーブルを作成してみます。

MySQLテーブルの文字コード

mysql> create table yy1 (yy2 text,yy3 text);
Query OK, 0 rows affected (0.01 sec)

テーブルの文字コードを見てみます。
mysql> show create table yy1 \G
*************************** 1. row ***************************
       Table: yy1
Create Table: CREATE TABLE `yy1` (
  `yy2` text,
  `yy3` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

CHARSET=latin1 となっています。
これが、日本語登録ができない原因です。

これをUTF8にするには、2つの方法があります。

1)SQLコマンドで変更する
2)SQLダンプで、書き出して、utf8に変更して読み込む。

1)SQLコマンドで変更する

mysql> alter table (テーブル名) convert to character set utf8;

↑と、行い、うまくいけば、
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

というように表示が出てきます。

しかし、

mysql> alter table (テーブル名) convert to character set utf8;
ERROR 1833 (HY000): Cannot change column 'xxxx': used in a foreign key constraint 'zzzzz_pkey' of table '(テーブル名)'

というエラーになる場合があります。
これは、「外部キー」が使われているため、SQLコマンドで、UTF8にするのは、面倒です・・・
この場合、次の方法で行う方が楽です。

2)SQLダンプで、書き出して、utf8に変更して読み込む。

A) データベースを書き出す(エキスポートする)
B) データベースを削除する
C) データベースを作成する
D) 書き出したデータベースの中身を変更する
E) データベースを読み込む(インポートする)

A) データベースを書き出す(エキスポートする)

コマンドラインから、

# /usr/bin/mysqldump --socket=/var/lib/mysql/mysql.sock -p(パスワード) --opt --single-transaction (データベース名) > (データベースファイル)

と書き出します。
SQLコマンドでも可能かと思います。

B) データベースを削除する

mysql> drop database (データベース名)

で、データベースを削除をします。

C) データベースを作成する

mysql> create database (データベース名)

↑と、あらためて、DBを作成します。

D) 書き出したデータベースの中身を変更する

A) で、書き出した、(データベースファイル)をテキストエディタで開いて、「latin1」というところを、「utf8」にします。

E) データベースを読み込む(インポートする)

コマンドラインから、

# mysql -u root -p (データベース名) <(データベースファイル)

とします。

「#」のコマンドプロンプトだけ、戻ってくれば、成功です。

根本的に文字コードをUTF8に設定

mysql> show variables like 'character%';

の文字コードを変更しましたが、データベースを再起動すると、元に戻ってしまいます。
また、毎回、DBを書き出したりするのも大変です。
根本的に、変更するには・・・
vi /etc/my.cnf

character-set-server=utf8

この3行を最後のほうに加えます。
[client]
default-character-set=utf8

↑[client]の記述は、不要でした。2020/05/17

character-set-server=utf8
は、
[mysqld]
のセクションに書きます。

最後に書くと、自動的に、[mysqld]のセクションになるので大丈夫かと!

この設定をすると、データベースの文字コード、テーブルの文字コードが、UTF8になって、エラーになることはないかと・・・

MySQL文字コード・・・

昔の資産を今の時代のMySQLで動かす場合、EUCである、ujisに設定したりして、コンテンツの寿命を延ばしています。

今の時代、UTF8にしておけば、大丈夫という感じになってきて、扱いやすい感じです・・・
文字コードを気にしないで、プログラムなどを組めるといいですね。

 

タイトルとURLをコピーしました