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

Comments are closed.

Post Navigation