Help us understand the problem. What is going on with this article?

[俺的コマンドまとめ] mysql5.7 文字コード と id で詰まったので、メモ。

More than 1 year has passed since last update.

目的

主に自分に向けての使用コマンドメモ。
今回のエラーはutf8 ascii 文字コード関係。
調べても全然出てこなかったので、自分でやってみた。
mysql5.7 テーブル構造などは簡略化してあります。
問題は記事の登録で、タイトルとコメントを追加しようとしたら、
SQLSTATE[HY000]: General error: 1366 Incorrect string value とでて、
string の形違いまっせ、と怒られたので、設定できなかったっけ?
と思って、振り返ってみた。

バージョン、説明ターゲット

  • mysql 5.7
  • windows10 ここではバージョン関係ないけど、

SQLSTATE[HY000]: General error: 1366 Incorrect string value のエラーで困っている人。
設定などのテンプレート記述が他ではわかりにくい人。(Syntax Errorとか出てたりする人。)

前提条件

mysql ログインできてて、もうデータベースがある。設定やエラーで困っている段階の人。

では本題

まずテーブルを作る。説明用に簡単にです。

mysql> create table comentRegister (
    -> id int,
    -> title text,
    -> coments text,
    -> create_time time,
    -> delete_time time);
Query OK, 0 rows affected (0.61 sec)

他にもkey等指定できますが、それは他の記事で。

mysql> show tables;
+--------------------+
| Tables_in_qiitasql |
+--------------------+
| comentregister     |
+--------------------+
1 row in set (0.02 sec)

mysql> show columns from comentregister;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| id          | int(11) | YES  |     | NULL    |       |
| title       | text    | YES  |     | NULL    |       |
| coments     | text    | YES  |     | NULL    |       |
| create_time | time    | YES  |     | NULL    |       |
| delete_time | time    | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+
5 rows in set (0.03 sec)

テーブルはいっぱいあるでしょうが、作ったテーブルの中はこんな感じ。
なにも設定されていないわけです。
これに追加すると、、

mysql> insert into comentregister (id,title,coments,create_time)
    -> values(
    -> 1,
    -> 'title',
    -> 'first coments!!',
    -> 15);
Query OK, 1 row affected (0.10 sec)

mysql> select * from comentregister;
+------+-------+-----------------+-------------+-------------+
| id   | title | coments         | create_time | delete_time |
+------+-------+-----------------+-------------+-------------+
|    1 | title | first coments!! | 00:00:15    | NULL        |
+------+-------+-----------------+-------------+-------------+
1 row in set (0.00 sec)

mysql>

この通り、普通に登録できます。
アルファベットですから。。。
でも、

mysql> insert into comentregister (id,title,coments,create_time)
    -> values(
    -> 2,
    -> "にほんごであそぼう",
    -> "漢字もだめですよ。",
    -> 24);

ERROR 1366 (HY000): Incorrect string value: '\x82\xE2\x82\xC1\x82\xD9...' for column 'title' at row 4

怒られます。。。

これで検索をかけると文字コードエラーということだけわかりました。
関連記事沢山ありますが、肝心な設定方法がねぇ!! となったのでw

それでは原因を確認しに!

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.7.24, for Win64 (x86_64)
~省略~
Current database:       qiitasql
~省略~
Server characterset:    cp932
Db     characterset:    cp932
Client characterset:    cp932
Conn.  characterset:    cp932
~省略~

--------------

mysql> show table status from qiitaSql;
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| 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 |
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| comentregister | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |            0 |         0 |           NULL | 2019-03-14 15:55:20 | 2019-03-14 16:24:12 | NULL       | cp932              |     NULL |                |         |
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

データベースの情報を見てみると、
Collation が cp932とかasciiとかになってたりするかと思います。。
windowsのコマンドプロンプトではデフォルトがcp932らしく、他ではまた違ってきます。
しかしこれが、エラーの原因!!!    見つけたぜ、やったぜ。

では、解決に!

別記事でも、my.iniとかの環境設定をしてると思いますが、
それは、テーブルを作る前段階でやらなければいけないこと!!
なので、補足でこの記事の最後に書きます。。。↓↓↓

テーブルに設定を加えていく方法! まず

変更したりするコマンド alter 細かい説明は他の記事にどうぞ!

alter table comentregister default character set utf8mb4 collate utf8mb4_unicode_ci;

これで、テーブルのcolleteがutf8mb4になりました。

  • comentregister : テーブル名
  • utf8mb4 : 文字コード
  • utf8mb4_unicode_ci: 登録されている形式  

このmb4というのは文字のデータ長が4ということ↓

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.02 sec)

これでサポートしているものが確認できる。
調べてもらうと分かるが、utf8mb4_unicode_ciはここにない。
これはutf8_general_ciと違うので、自分で検索してみてください。

windowsの場合、
C:\Program Files\MySQL\MySQL Server 5.7\share\charsets
に入っているサポートが表示される。
文字コードの詳細についても公式を見てほしい。

さて、さっきできなかったデータの登録。

mysql> insert into comentregister (id,title,coments,create_time)
    -> values(
    -> 2,
    -> "にほんごであそぼう",
    -> "漢字もOKかな?",
    -> 24);
Query OK, 1 row affected (0.11 sec)

mysql> select * from comentregister ;
+------+--------------------+-----------------+-------------+-------------+
| id   | title              | coments         | create_time | delete_time |
+------+--------------------+-----------------+-------------+-------------+
|    1 | title              | first coments!! | 00:00:15    | NULL        |
|    2 | にほんごであそぼう   | 漢字もOKかな?   | 00:00:24    | NULL        |
+------+--------------------+-----------------+-------------+-------------+
2 rows in set (0.00 sec)

登録できたでぞ!!!  ちょっと変更もしてみようか。。

mysql> update comentregister set coments="漢字OKでした!(>ω<)/" where id=2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from comentregister ;
+------+--------------------+-------------------------+-------------+-------------+
| id   | title              | coments                 | create_time | delete_time |
+------+--------------------+-------------------------+-------------+-------------+
|    1 | title              | first coments!!         | 00:00:15    | NULL        |
|    2 | にほんごであそぼう | 漢字OKでした!(>ω<)/ | 00:00:24    | NULL        |
+------+--------------------+-------------------------+-------------+-------------+
2 rows in set (0.04 sec)

さっきのgeneralでやると、登録できない文字もあるはず。。
登録できる文字に関しては、調べてください。めちゃくちゃ多いので、
ただ、日本で扱うのであれば、基本asciiかutf8あたりが主流だと思います。
デフォルトがどうしてもね。。

※あ、これは絶対に気を付けること!!
文字コードを変更したはいいものの、データがすでに登録されている場合、そのデータのエンコードは前の物。つまり文字化けのオンパレード!!データを移行しておきましょう。。

まとめ

困ったら、分かる人に聞くか、バックアップして、自分で色々試すくらいしか方法はない。
調べて完全にすべて分かったら、そいつは天才だよ。。

今回は、文字コードということで、環境がしっかりしていれば、
こんなエラーは起きなかった。反省反省。。時間にして4時間。
主に解決方法がわからず、設定が反映されず、どういう仕組みかを理解するのに時間がかかった。

環境を事前に設定、あるいは再起動 my.ini

mysql>  show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | cp932                                                   |
| character_set_connection | cp932                                                   |
| character_set_database   | cp932                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | cp932                                                   |
| character_set_server     | cp932                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.09 sec)

こんな感じになってるかと。。。 このcp932をutf8系に変えたい。

この作業はテーブル作成前に行います。もうある場合は作り直しでやることも可能。。
その時はデータを退避させておきましょう。消えます。

まず、mysql ログアウト。 停止。

net stop mysql57 

とコマンド打ってもいいし、windows < サービスから停止でもいいです。
macとか windowsの機体によってはコマンドで実行できないかも。。
コルタナでサービスを検索すればサービス画面出てきます。そこから消せますんで。。
mysql57はサービス名 net helpからサービスが確認できるかと。。

では、my.iniを探します。
windowsでは、C:\ProgramData\MySQL\MySQL Server 5.7 < my.ini 
隠しフォルダになっているので、ちゃんと探しましょう。
サービス系なので、たぶんここ以外にある人は、動いてないと思いますw
バージョンが複数ある人は、ちゃんと今起動しているmysqlのバージョンを見ます。

mysqlをインストールしたときとかに設定はしていると思いますが、それです。
探せたら、管理者権限でテキストエディターを起動します。
コマンドラインで変更できる人はそれでも可。とにかく変更を加えたいので、管理者で。
簡単なのは「メモ帳」かな? アプリから右クリック管理者で起動を選択。
管理者起動メモ帳.jpg
管理者じゃないとパスワード必要なので、わからないと、mysql 再インストールで設定するしかないです。。

inimy1.jpg
inimy2.jpg

画像のように同じ場所を設定します。(画像通りにやればOK)
「# default-character-set= 」となっていると思います。
「#」はコメントアウトなので、消して、~~set=oooooooo と設定します。
その他の設定は自分で調べてみてください。

my.iniを設定できて保存したら、閉じてOK.
コマンドに戻って、net start mysql57 (サービスからの起動可)
iniがおかしいと、エラーでて、起動できません。 →スペルチェック!

では、再度設定を見ましょう。

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.24, for Win64 (x86_64)
~省略~
Current database:       qiitasql
~省略~
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    cp932
Conn.  characterset:    cp932
~省略~
--------------

さて、変わっているでしょうか?保存で来てなかったり、
mysql停止してからやってなかったりすると、変更は反映されませんよ。

この状態で、table を作成してみると、、、
見た目変化ありません(defaultと表示されているのでww)が、
text やvarchar()で日本語や絵文字がそのまま追加できるはずですよ。

基本はこっちの環境を整えてからデータベースを作っていく形にしましょう。
後々面倒です。最初の仕様作成や機能要件定義は重要ということですね。
まあ、ユニコードとかは、もうutf-8がデフォルトでも、
特段問題はないと思いますがね。。。

id 連番で自動追加してもらう

これは補足 自分メモです。
idをいちいち入れて追加するのは嫌だ。
外部から行う時、idの値をいちいち取得するの??嫌だ。。
ということで、データベース側で、ちゃんと連番にしてくれます。

まず状態を確認

mysql> show fields from comentregister;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| id          | int(11) | NO   |     | NULL    |       |
| title       | text    | YES  |     | NULL    |       |
| coments     | text    | YES  |     | NULL    |       |
| create_time | time    | YES  |     | NULL    |       |
| delete_time | time    | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+
5 rows in set (0.03 sec)

こうやると、今の状態を確認できます。
今回テーブルに何も設定してないので、何もない。

ではまず、下準備。

mysql> alter table comentregister add primary key (id);
Query OK, 0 rows affected (1.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

この処理ではprimary key を設定しています。分かってる人は飛ばして次に
Primary keyとは主キーのこと。主にidや商品番号、製造番といったように、
一意(ユニーク、唯一)のキーということで、そのデータは一つしかないよ、という証明と、
指定する際の指標として使います。このテーブル内に同じ値が複数は絶対に存在できません。
例えば、同じタイトル、コメントの記事ができたとします。
時間も同じ、じゃあ、どっちも指定したい時にどうするの?ってなると、一意のidがなければ、
曖昧で指定ができなくなります。
そこで、ユニークな主キー! これは同じにならないものなので、これで判定すればいいのです。
ということは、そういう判断せずに勝手に作ってほしいですよね?!
今回は連番にしていきます。https://qiita.com/sakuraya/items/0dd0bb4114e56f42556d 参考にしてください。

mysql> show fields from comentregister;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| id          | int(11) | NO   | PRI | NULL    |       |
| title       | text    | YES  |     | NULL    |       |
| coments     | text    | YES  |     | NULL    |       |
| create_time | time    | YES  |     | NULL    |       |
| delete_time | time    | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+
5 rows in set (0.03 sec)

PRIが追加されましたね。主キーとなっていないとエラーがでます。
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
keyの設定されてるカラムだけです、と怒られます。

mysql> alter table comentregister change id id int auto_increment;
Query OK, 2 rows affected (1.17 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show fields from comentregister;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| title       | text    | YES  |     | NULL    |                |
| coments     | text    | YES  |     | NULL    |                |
| create_time | time    | YES  |     | NULL    |                |
| delete_time | time    | YES  |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

extraにauto_incrementが追加されました。やった。
じゃ、確認!! id 指定せずに追加してみましょう。

mysql> insert into comentregister (title,coments,create_time)
    -> values(
    -> "タイトル",
    -> "ここに記事を書いていくよー。",
    -> 30);
Query OK, 1 row affected (0.11 sec)

mysql> select * from comentregister;
+----+--------------------+------------------------------+-------------+-------------+
| id | title              | coments                      | create_time | delete_time |
+----+--------------------+------------------------------+-------------+-------------+
|  1 | title              | first coments!!              | 00:00:15    | NULL        |
|  2 | にほんごであそぼう | 漢字OKでした!(>ω<)/      | 00:00:24    | NULL        |
|  3 | タイトル           | ここに記事を書いていくよー。 | 00:00:30    | NULL        |
+----+--------------------+------------------------------+-------------+-------------+
3 rows in set (0.00 sec)

auto_incrementができてないと、

mysql> select * from comentregister;
+-------+--------------------+------------------------------+-------------+-------------+
| id    | title              | coments                      | create_time | delete_time |
+-------+--------------------+------------------------------+-------------+-------------+
|  1    | title              | first coments!!              | 00:00:15    | NULL        |
|  2    | にほんごであそぼう | 漢字OKでした!(>ω<)/      | 00:00:24    | NULL        |
|  null | タイトル           | ここに記事を書いていくよー。 | 00:00:30    | NULL        |
+----+--------------------+------------------------------+-------------+-------------+
3 rows in set (0.00 sec)

となって、id不明のままですね。
こういった環境の設定は最初にやりましょうということで!

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした