MySQL、文字コードの基本・・・
改めて、整理を・・・（笑）
PythonでＤＢ、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)
↑と、ＳＱＬコマンドで変更することができます。
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になり、意図通りになりますが、ＤＢを再起動すると、元に戻ります。
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にするには、２つの方法があります。
１）SQLコマンドで変更する
２）SQLダンプで、書き出して、utf8に変更して読み込む。
１）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にするのは、面倒です・・・
この場合、次の方法で行う方が楽です。
２）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 （データベース名）
↑と、あらためて、ＤＢを作成します。
D) 書き出したデータベースの中身を変更する
A) で、書き出した、（データベースファイル）をテキストエディタで開いて、「latin1」というところを、「utf8」にします。
E) データベースを読み込む（インポートする）
コマンドラインから、
# mysql -u root -p （データベース名） <（データベースファイル）
とします。
「#」のコマンドプロンプトだけ、戻ってくれば、成功です。
根本的に文字コードをUTF8に設定
mysql> show variables like 'character%';
の文字コードを変更しましたが、データベースを再起動すると、元に戻ってしまいます。
また、毎回、ＤＢを書き出したりするのも大変です。
根本的に、変更するには・・・
vi /etc/my.cnf character-set-server=utf8
この３行を最後のほうに加えます。
[client] default-character-set=utf8
↑[client]の記述は、不要でした。2020/05/17
character-set-server=utf8
は、
[mysqld]
のセクションに書きます。
最後に書くと、自動的に、[mysqld]のセクションになるので大丈夫かと！
この設定をすると、データベースの文字コード、テーブルの文字コードが、UTF8になって、エラーになることはないかと・・・
MySQL文字コード・・・
昔の資産を今の時代のMySQLで動かす場合、EUCである、ujisに設定したりして、コンテンツの寿命を延ばしています。
今の時代、UTF8にしておけば、大丈夫という感じになってきて、扱いやすい感じです・・・
文字コードを気にしないで、プログラムなどを組めるといいですね。
