CASE文などMYSQLでの制御フローは色々ありますが,
今回は以前検証したIF文の復習です。

制御フロー関数 IF
Unique Key が存在するテーブルへのデータINSERT処理


mysql> CREATE TABLE `USER` (
-> `id` int(4) DEFAULT NULL,
-> `NAME` varchar(20) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> desc USER;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| NAME | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into USER values(1,'USER1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into USER values(2,'USER2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into USER values(NULL,'USER3');
Query OK, 1 row affected (0.01 sec)

mysql> select * from USER;
+------+-------+
| id | NAME |
+------+-------+
| 1 | USER1 |
| 2 | USER2 |
| NULL | USER3 |
+------+-------+
3 rows in set (0.00 sec)

mysql> UPDATE USER SET id=IF(id is not null,id+1,0);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from USER;
+------+-------+
| id | NAME |
+------+-------+
| 2 | USER1 |
| 3 | USER2 |
| 0 | USER3 |
+------+-------+
3 rows in set (0.00 sec)

mysql> UPDATE USER SET id=IF(id is not null,id+1,0);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from USER;
+------+-------+
| id | NAME |
+------+-------+
| 3 | USER1 |
| 4 | USER2 |
| 1 | USER3 |
+------+-------+
3 rows in set (0.00 sec)

mysql>

user_test


【データベースサイズの確認】

root@information_schema>select table_schema, sum(data_length+index_length) /1024 /1024 as MB
-> from information_schema.tables group by table_schema order by sum(data_length+index_length) desc;

+——————–+————-+
| table_schema | MB |
+——————–+————-+
| DB01 | 13.41426182 |
| DB02 | 9.38685513 |
| DB03 | 8.78778076 |
| DB04 | 1.59736252 |
| DB05 | 0.69472599 |
| mysql | 0.58031273 |
| DB06 | 0.28602695 |
| DB07 | 0.25055885 |
| test | 0.03125000 |
| information_schema | 0.00781250 |
+——————–+————-+
10 rows in set (0.04 sec)

root@information_schema>

【以下実際のデータベースフォルダー】

[root@SRV01 data]# du -h
14M ./DB01
9.9M ./DB02
9.2M ./DB03
1.9M ./DB04
1020K ./DB05
1.3M ./mysql
604K ./DB06
1.2M ./DB07
40K ./test
[root@SRV01 data]#