データベースにデータを追加
この前作ったcsvからデータをデータベースにぶっこんでいきたい。どうやるんだろうなーとプログラム書いて一行ずつインサートとかかなと思ったら、ググったところcsvから読み込むコマンドがあるらしい。丁度いいのでそれを使おう。ブログ作った時にデータベースも作ってあるはずなので、それに新規テーブルを作ればいいはず。使ってるのはMariaDBだけどMySQLの構文でイケるみたい。まぁどっちも知らんのだけど。
データベースのパスワード忘れた
やべぇ、どうしよう。色々探したら、ワードプレスのwp-config.phpに書いてあるらしい。開いてみたらそのものずばり書いてあったので事なきを得た。あぶねー。
とりあえず試す
まずは慣れようと思ったので、試しにテストテーブル作ってみる。
mysql -u root -pで、パスワード聞かれるのでログイン。
show databases;で今のデータベースを見てみる。
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wordpressdb |
+--------------------+
4 rows in set (0.00 sec)おー、でた。すごーい、プログラマーみたい。じゃあ、
create database test;
Query OK, 1 row affected (0.00 sec)作れたっぽい。もっかいshow
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wordpressdb |
+--------------------+
5 rows in set (0.00 sec)いやっふ～。
use test;
Database changedtestに接続。なんかテーブル作ろ。んー
create table list (no int, name text);
Query OK, 0 rows affected (0.31 sec)できたかな？確認は
show tables;
+----------------+
| Tables_in_test |
+----------------+
| list |
+----------------+
1 row in set (0.00 sec)いーねー。じゃ基本らしい4つを
INSERT 挿入
MariaDB [test]> insert into list (no, name) values (1, 'yamada');
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into list (no, name) values (2, 'suzuki');
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into list (no, name) values (3, 'tanaka');
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> insert into list (no, name) values (4, '遠藤');
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [test]> insert into list (no, name) values (5, '松本');
Query OK, 1 row affected, 1 warning (0.00 sec)上3つはいい感じ、下はなんかwarningってなってるね。これを見るにはー？
show warnings;で、見れるみたい。でも直前のだけだって。じゃもっかいinsertして直後にやってみると
MariaDB [test]> insert into list (no, name) values (6, '浜田');
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [test]> show warnings;
+---------+------+-------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xE6\xB5\x9C\xE7\x94\xB0' for column 'name' at row 1 |
+---------+------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)ほーん？なんか文字コードの問題らしい。
MariaDB [test]> show variables like "chara%";
+--------------------------+----------------------------+
| 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)これを、utf8mb4に変えればいいみたい。みたい。でもさ、今の今までブログでデータベースは自動で使ってきたようなもんじゃん。これ変えちゃって他に影響とかでないのかなぁ？見た感じでしかないけど他のデータベースと共用の設定じゃないのかなこれ。確認してみよう。
MariaDB [test]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| wordpressdb |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [test]> use wordpressdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [wordpressdb]> show variables like "chara%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| 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)あー、character_set_databaseのとこが違うね。てことはデータベースごとに違う設定になってるんか。じゃtestデータベースもcharacter_set_databaseのとこだけ変えれればそれでいいのかな？どうやるのかなっと
MySQLの文字コード変更
MySQLを5.7から8へバージョンアップする過程で、文字コードをutf8mb4に変更する必要が生じたため、方法をメモします。
hodalog.com
こちらを参考にして既存のやつを換えるコマンドを叩く。
MariaDB [test]> ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_genera
l_ci;
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> ALTER TABLE list CONVERT TO character SET utf8mb4 COLLATE utf8mb
4_unicode_ci;
Query OK, 6 rows affected (0.04 sec)
Records: 6 Duplicates: 0 Warnings: 0
MariaDB [test]> ALTER TABLE list CHANGE name name TEXT CHARACTER SET utf8mb4 COL
LATE utf8mb4_unicode_ci;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0どーだろ、ダイジョブかな。もっかいINSERTする。
MariaDB [test]> insert into list (no, name) values (7, '斎藤');
Query OK, 1 row affected (0.01 sec)おっけーそう！
SELECT 選択
とりあえず全部
MariaDB [test]> select * from list;
+------+--------+
| no | name |
+------+--------+
| 1 | yamada |
| 2 | suzuki |
| 3 | tanaka |
| 4 | ?? |
| 5 | ?? |
| 6 | ?? |
| 7 | 斎藤 |
+------+--------+
7 rows in set (0.01 sec)あれれー…4, 5, 6が?になっちゃったか。んー、スルーしよう。とにかく文字コードが悪かったんだな。次から気をつける。にしてもどうやるのがいいんだろうなぁ。SELECTは練習するにしてもいっぱいあるのでこれだけでいいや。
UPDATE 更新
MariaDB [test]> update list set name="遠藤" where no=4;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> update list set name="松本" where no=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> update list set name="浜田" where no=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> select * from list;
+------+--------+
| no | name |
+------+--------+
| 1 | yamada |
| 2 | suzuki |
| 3 | tanaka |
| 4 | 遠藤 |
| 5 | 松本 |
| 6 | 浜田 |
| 7 | 斎藤 |
+------+--------+
7 rows in set (0.00 sec)できたー。
DELETE 削除
MariaDB [test]> delete from list where no=2;
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> delete from list where no=4;
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> delete from list where no=6;
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> delete from list where no=8;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> select * from list;
+------+--------+
| no | name |
+------+--------+
| 1 | yamada |
| 3 | tanaka |
| 5 | 松本 |
| 7 | 斎藤 |
+------+--------+
4 rows in set (0.00 sec)おっけーですね。とりあえず触り方はなんとなく分かったので削除しましょ。
MariaDB [test]> drop database test;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wordpressdb |
+--------------------+
4 rows in set (0.00 sec)消えた。じゃーいよいよ本番のやつ作ろう。
本番のやつ作る
database
MariaDB [(none)]> create database shogi character set utf8mb4;
Query OK, 1 row affected (0.00 sec)character set **で、文字コードを設定できることを知ったので、やっておいてみる。
table
MariaDB [shogi]> create table data ( id int not null auto_increment, date date,
title text, maker text, steps int, url text, primary key (id));
Query OK, 0 rows affected (0.00 sec)一意の値ってなると、日付はそうっぽいんだけど、日付を主キーに設定するのってどうなのかよく分かんないからidカラムを作ってオートインクリメントにしてみた。dateカラムは型名と一緒じゃん！って思ったけど、やってみたら通ったのでいいみたい。title, maker, urlは脳死でTEXT型にした。なんかVARCHARでちゃんと指定したほうが、パフォーマンスいいみたいだけど、まぁいいやの精神。stepsは一応INT型にした。別に計算するつもりもないのだけれど。date, title, steps, urlにはnot nullつけるべきなのかもしれない。
LOAD DATA INFILE
作成したcsvファイルを適当な場所において、LOAD DATA INFILE構文を使用してテーブルに読み込めるそう。とりあえずやってみる。
MariaDB [shogi]> load data local infile "shogi.csv" into table data fields terminated by ',';
Query OK, 5 rows affected, 6215 warnings (0.02 sec)
Records: 1244 Deleted: 0 Skipped: 1239 Warnings: 6215よっこいしょー！わぁっ！Warningsが出ちゃった…。とりあえずWarnings見よう…
MariaDB [shogi]> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'date' for column 'id' at row 1 |
| Warning | 1265 | Data truncated for column 'date' at row 1 |
' for column 'steps' at row 1 |
| Warning | 1261 | Row 1 doesn't contain data for all columns |
| Warning | 1265 | Data truncated for column 'id' at row 2 |
| Warning | 1265 | Data truncated for column 'date' at row 2 |
| Warning | 1366 | Incorrect integer value: 'https://www.shogi.or.jp/tsume_shogi' for column 'steps' at row 2 |
| Warning | 1261 | Row 2 doesn't contain data for all columns |
| Warning | 1062 | Duplicate entry '2017' for key 'PRIMARY' |
| Warning | 1265 | Data truncated for column 'id' at row 3 |
| Warning | 1265 | Data truncated for column 'date' at row 3 |
| Warning | 1366 | Incorrect integer value: 'https://www.shogi.or.jp/tsume_shogi' for column 'steps' at row 3 |
| Warning | 1261 | Row 3 doesn't contain data for all columns |
略
| Warning | 1062 | Duplicate entry '2017' for key 'PRIMARY' |
| Warning | 1265 | Data truncated for column 'id' at row 13 |
| Warning | 1265 | Data truncated for column 'date' at row 13 |
| Warning | 1366 | Incorrect integer value: 'https://www.shogi.or.jp/tsume_shogi' for column 'steps' at row 13 |
| Warning | 1261 | Row 13 doesn't contain data for all columns |
| Warning | 1062 | Duplicate entry '2017' for key 'PRIMARY' |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
64 rows in set (0.00 sec)あととりまSELECT
MariaDB [shogi]> select * from data;
+------+------------+--------------+-------+-------+------+
| id | date | title | maker | steps | url |
+------+------------+--------------+-------+-------+------+
| 1 | 0000-00-00 | maker | steps | 0 | NULL |
| 2017 | 0000-00-00 | 内藤國雄 | 11 | 0 | NULL |
| 2018 | 0000-00-00 | 屋敷伸之 | 11 | 0 | NULL |
| 2019 | 0000-00-00 | 谷川浩司 | 11 | 0 | NULL |
| 2020 | 0000-00-00 | 谷川浩司 | 11 | 0 | NULL |
+------+------------+--------------+-------+-------+------+
5 rows in set (0.00 sec)なるほど…。俺はこのコマンドはカラム名から判断してなんかいい感じに処理してくれたりすんのかなとか思ってたけどそういうわけじゃないみたい。
- csvの最初の行に記載してた列名もテーブルに読み込もうとしてエラー
- csv内にid列に対する列がないのでその分読み込み列が一個ずつ前にずれた。
- id列は数え上げint主キーなので年の最初の行だけ読み込んで以降はエラー
みたいな感じかなぁ。csvの最初の行消して、id列を追加してもう一回やってみよう。
MariaDB [shogi]> drop table data;
Query OK, 0 rows affected (0.12 sec)
MariaDB [shogi]> show tables;
Empty set (0.00 sec)
MariaDB [shogi]> create table data ( id int not null auto_increment, date date, title text, maker text, steps int, url text, primary key (id));
Query OK, 0 rows affected (0.00 sec)
MariaDB [shogi]> load data local infile "shogi.csv" into table data fields terminated by ',';
Query OK, 1243 rows affected (0.30 sec)
Records: 1243 Deleted: 0 Skipped: 0 Warnings: 0お、いけたっぽい。
MariaDB [shogi]> select * from data where id=1234;
+------+------------+--------------------------------------------------------------+--------------+-------+--------------------------------------------------------------+
| id | date | title | maker | steps | url |
+------+------------+--------------------------------------------------------------+--------------+-------+--------------------------------------------------------------+
|1234 | 2020-08-16 | 2020年8月16日の詰将棋（村田顕弘作、11手詰） | 村田顕弘 | 11 | https://www.shogi.or.jp/tsume_shogi/everyday/202081611.html
+------+------------+--------------------------------------------------------------+--------------+-------+--------------------------------------------------------------+
1 row in set (0.00 sec)おお！いいんでないかな？よっしゃよっしゃ、今日はここまで～。こっからだなぁ～。
コメント