はじめに
MySQLのinsert、update、delete関連についてまとめる。
insert
通常のinsert方法は下記の通り。insert into <table name> (col1, col2,...,coln) values(*,*,...,*)という文法。
mysql> CREATE TABLE tbl01 (
id INT,
val INT,
name CHAR(30)
character set utf8
);
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert into tbl01 (id, val, name) values(1, 100, "1回目のインサート");
Query OK, 1 row affected (0.01 sec)
mysql> select * from tbl01;
+------+------+---------------------------+
| id | val | name |
+------+------+---------------------------+
| 1 | 100 | 1回目のインサート |
+------+------+---------------------------+
1 row in set (0.01 sec)
mysql> insert into tbl01 (id, val, name) values(2, 200, "2回目のインサート");
Query OK, 1 row affected (0.00 sec)
mysql> select * from tbl01;
+------+------+---------------------------+
| id | val | name |
+------+------+---------------------------+
| 1 | 100 | 1回目のインサート |
| 2 | 200 | 2回目のインサート |
+------+------+---------------------------+
2 rows in set (0.00 sec)
特定のカラムにのみ値を入れる場合は、そのカラム名を残して指定する。
mysql> insert into tbl01 (id, name) values(3, "3回目のインサート"); Query OK, 1 row affected (0.00 sec) mysql> select * from tbl01; +------+------+---------------------------+ | id | val | name | +------+------+---------------------------+ | 1 | 100 | 1回目のインサート | | 2 | 200 | 2回目のインサート | | 3 | NULL | 3回目のインサート | +------+------+---------------------------+ 3 rows in set (0.00 sec)
bulk insert
バルクインサートする場合は、valuesの後に値を連ねていく。バルクインサートのほうがSQLを解釈する回数が減るので処理が速い。
mysql> insert into tbl01 values (4, 400, "4回目のインサート"), (5, 500, "5回目のインサート"), (6, 600, "6回目のインサート"), (7, 700, "7回目のインサート"), (8, 800, "8回目のインサート"), (9, 900, "9回目のインサート"), (10, 1000, "10回目のインサート") ; Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from tbl01; +------+------+----------------------------+ | id | val | name | +------+------+----------------------------+ | 1 | 100 | 1回目のインサート | | 2 | 200 | 2回目のインサート | | 3 | NULL | 3回目のインサート | | 4 | 400 | 4回目のインサート | | 5 | 500 | 5回目のインサート | | 6 | 600 | 6回目のインサート | | 7 | 700 | 7回目のインサート | | 8 | 800 | 8回目のインサート | | 9 | 900 | 9回目のインサート | | 10 | 1000 | 10回目のインサート | +------+------+----------------------------+ 10 rows in set (0.00 sec)
csvインポート
csvでインポートする方法をまとめる。まずはテーブルの中身を削除する。
mysql> truncate tbl01; Query OK, 0 rows affected (0.01 sec) mysql> select * from tbl01; Empty set (0.01 sec)
インポートするcsvはこんな感じ。
id,val,name 1,100,"1回目のインサート" 2,200,"2回目のインサート" 3,300,"3回目のインサート" 4,400,"4回目のインサート" 5,500,"5回目のインサート"
load data infileは、テキストファイルからテーブルに行を読み取ってくれるコマンド。
mysql> LOAD DATA INFILE '~/Desktop/sample.csv' INTO TABLE hatena.tbl01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES; Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from tbl01; +------+------+---------------------------+ | id | val | name | +------+------+---------------------------+ | 1 | 100 | 1回目のインサート | | 2 | 200 | 2回目のインサート | | 3 | 300 | 3回目のインサート | | 4 | 400 | 4回目のインサート | | 5 | 500 | 5回目のインサート | +------+------+---------------------------+ 5 rows in set (0.00 sec)
下記のcsvのようにvalやnameに空の値があると読み込めない。
id,val,name 1,100,"1回目のインサート" 2,200,"2回目のインサート" 3,300,"3回目のインサート" 4,,"4回目のインサート" 5,500, mysql> truncate tbl01; Query OK, 0 rows affected (0.02 sec) mysql> LOAD DATA INFILE '~/Desktop/sample.csv' INTO TABLE hatena.tbl01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES; ERROR 1366 (HY000): Incorrect integer value: '' for column 'val' at row 4
その場合は、このパターンだったらNULLに変換、というコマンドを追加する必要がある。IGNORE 1 LINESの下の部分がそれに当たる。
mysql> LOAD DATA INFILE '~/Desktop/sample.csv' INTO TABLE hatena.tbl01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES ( @var_id, @var_val, @var_name) set `id` = nullif(@var_id, ""), `val` = nullif(@var_val, ""), `name` = nullif(@var_name, ""); Query OK, 5 rows affected (0.01 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from tbl01; +------+------+---------------------------+ | id | val | name | +------+------+---------------------------+ | 1 | 100 | 1回目のインサート | | 2 | 200 | 2回目のインサート | | 3 | 300 | 3回目のインサート | | 4 | NULL | 4回目のインサート | | 5 | 500 | NULL | +------+------+---------------------------+ 5 rows in set (0.00 sec)
イベントスケジューラー
MySQLには、イベントスケジューラーという機能があり、スケジューラーを設定すれば、その時間に合わせてSQLを実行してくれる。
まずは、イベントスケジューラーを起動する。
mysql> SET GLOBAL event_scheduler = ON; mysql> SHOW variables like 'event%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+
ここでは、10秒ごとにデータをテーブルに自動で生成するイベントを使用する。
CREATE TABLE auto_insert_time_tbl (
id INT AUTO_INCREMENT,
time DATETIME NOT NULL,
val INT NOT NULL,
PRIMARY KEY (id)
);
create event auto_insert_time_to_tbl
on schedule every 10 SECOND
starts now()
do INSERT INTO auto_time.auto_insert_time_tbl(time, val) VALUES (NOW(), FLOOR(RAND() * 100));
実行した直後からイベントが実行されるように設定したの、すぐにこんな感じになる。
mysql> select * from auto_insert_time_tbl; +----+---------------------+-----+ | id | time | val | +----+---------------------+-----+ | 1 | 2019-11-01 16:44:37 | 44 | | 2 | 2019-11-01 16:44:47 | 74 | | 3 | 2019-11-01 16:44:57 | 4 | | 4 | 2019-11-01 16:45:07 | 48 | | 5 | 2019-11-01 16:45:17 | 27 | | 6 | 2019-11-01 16:45:27 | 31 | | 7 | 2019-11-01 16:45:37 | 90 | | 8 | 2019-11-01 16:45:47 | 70 | | 9 | 2019-11-01 16:45:57 | 33 | | 10 | 2019-11-01 16:46:07 | 4 | | 11 | 2019-11-01 16:46:17 | 10 | | 12 | 2019-11-01 16:46:27 | 88 | | 13 | 2019-11-01 16:46:37 | 14 | | 14 | 2019-11-01 16:46:47 | 93 | | 15 | 2019-11-01 16:46:57 | 2 | | 16 | 2019-11-01 16:47:07 | 49 | | 17 | 2019-11-01 16:47:18 | 18 | | 18 | 2019-11-01 16:47:27 | 70 | +----+---------------------+-----+ 18 rows in set (0.00 sec)
不要であれば、イベントスケジューラーをOFFにするか、イベントを削除する。
mysql> SET GLOBAL event_scheduler = OFF; mysql> SHOW variables like 'event%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ #---------------------------- mysql> use auto_time; mysql> DROP EVENT IF EXISTS auto_insert_time_to_tbl; Query OK, 0 rows affected, 1 warning (0.00 sec)
update
インサートしたデータを更新する場合に使うコマンドがupdate。where句でレコードを特定し、カラムの値を更新する。
mysql> update tbl01 set val=400 where id = 4; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update tbl01 set name="5回目のインサート" where id = 5; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tbl01; +------+------+---------------------------+ | id | val | name | +------+------+---------------------------+ | 1 | 100 | 1回目のインサート | | 2 | 200 | 2回目のインサート | | 3 | 300 | 3回目のインサート | | 4 | 400 | 4回目のインサート | | 5 | 500 | 5回目のインサート | +------+------+---------------------------+ 5 rows in set (0.00 sec)
delete
インサートしたデータを削除する場合に使うコマンドがdelete。where句でレコードを特定し、カラムの値を削除する。
mysql> delete from tbl01 where id = 3; Query OK, 1 row affected (0.01 sec) mysql> select * from tbl01; +------+------+---------------------------+ | id | val | name | +------+------+---------------------------+ | 1 | 100 | 1回目のインサート | | 2 | 200 | 2回目のインサート | | 4 | 400 | 4回目のインサート | | 5 | 500 | 5回目のインサート | +------+------+---------------------------+ 4 rows in set (0.00 sec)
