MySQL如何形成MySQL条件插入

您可以使用MySQL双表进行插入。让我们创建一个表来了解条件插入的概念。创建表的查询如下所示 –

mysql> create table ConditionalInsertDemo
   -> (
   -> UserId int,
   -> TotalUser int,
   -> NumberOfItems int
   -> );
Query OK, 0 rows affected (0.58 sec)

使用插入语句将一些记录插入表中。查询如下所示 –

mysql> insert into ConditionalInsertDemo values(101,560,780);
Query OK, 1 row affected (0.19 sec)

mysql> insert into ConditionalInsertDemo values(102,660,890);
Query OK, 1 row affected (0.20 sec)

mysql> insert into ConditionalInsertDemo values(103,450,50);
Query OK, 1 row affected (0.15 sec)

使用SELECT语句显示表中的所有记录。查询如下所示 –

mysql> select *from ConditionalInsertDemo;

阅读更多：MySQL 教程

输出

+--------+-----------+---------------+
| UserId | TotalUser | NumberOfItems |
+--------+-----------+---------------+
|    101 | 560       |           780 |
|    102 | 660       |           890 |
|    103 | 450       |            50 |
+--------+-----------+---------------+
3 rows in set (0.00 sec)

现在表中有3条记录。使用双表的条件插入帮助插入下一条记录。根据条件，当UserId=104且NumberOfItems=3500不在表中时，查询将记录插入表中。条件插入查询如下 –

mysql> insert into ConditionalInsertDemo(UserId,TotalUser,NumberOfItems)
   -> select 104,900,3500 from dual
   -> WHERE NOT EXISTS (SELECT * FROM ConditionalInsertDemo
   -> where UserId=104 and NumberOfItems=3500);
Query OK, 1 row affected (0.18 sec)
Records: 1 Duplicates: 0 Warnings: 0

现在您可以检查表格，确定是否插入了记录。查询显示所有记录如下 –

mysql> select *from ConditionalInsertDemo;

输出

+--------+-----------+---------------+
| UserId | TotalUser | NumberOfItems |
+--------+-----------+---------------+
|    101 |       560 |           780 |
|    102 |       660 |           890 |
|    103 |       450 |            50 |
|    104 |       900 |          3500 |
+--------+-----------+---------------+
4 rows in set (0.00 sec)