mysql的dual

来源：互联网发布：linux命令ls -lre意思编辑：程序博客网时间：2024/06/10 09:40

MySQL 中的 dual 表
因为 MySQL 和 SQL Server 中，可以
SELECT 1;
不需要 FROM 表。
所以从来没有考虑过 Oracle 里面的 dual 表，在 MySQL 当中要如何处理。

mysql> select * from test_book1;
+------+-----------+--------------------------------+
| id | name | data |
+------+-----------+--------------------------------+
| 1 | 测试数据1 | 测试CSV文件中,有逗号 |
| 2 | 测试数据2 | 测试CSV文件中有"双引号" |
| 3 | 测试数据3 | 测试CSV文件中,有逗号和"双引号" |
| 4 | 测试数据4 | 普通数据 |
+------+-----------+--------------------------------+
4 rows in set (0.01 sec)

最终用户希望在插入数据库表以前，先判断数据是否已存在
mysql> INSERT INTO test_book1
-> SELECT 1, 'TEST', 'ABC'
-> WHERE NOT EXISTS( SELECT 1 FROM test_book1 WHERE id = 1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'WHERE NOT EXISTS( SELECT 1 FROM test_book1 WH
ERE id = 1)' at line 3
结果出错了。

然后尝试单独写一下 SELECT 语句
mysql> SELECT 1, 'TEST', 'ABC'
-> WHERE NOT EXISTS( SELECT 1 FROM test_book1 WHERE id = 1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'WHERE NOT EXISTS( SELECT 1 FROM test_book1 WH
ERE id = 1)' at line 2
结果还是出错了。

加上一句 FROM dual 以后，正常了。
mysql> SELECT 1, 'TEST', 'ABC' FROM dual
-> WHERE NOT EXISTS( SELECT 1 FROM test_book1 WHERE id = 1);
Empty set (0.00 sec)

mysql> INSERT INTO test_book1
-> SELECT 1, 'TEST', 'ABC' FROM dual
-> WHERE NOT EXISTS( SELECT 1 FROM test_book1 WHERE id = 1);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> INSERT INTO test_book1
-> SELECT 5, 'TEST', 'ABC' FROM dual
-> WHERE NOT EXISTS( SELECT 1 FROM test_book1 WHERE id = 5);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from test_book1;
+------+-----------+--------------------------------+
| id | name | data |
+------+-----------+--------------------------------+
| 1 | 测试数据1 | 测试CSV文件中,有逗号 |
| 2 | 测试数据2 | 测试CSV文件中有"双引号" |
| 3 | 测试数据3 | 测试CSV文件中,有逗号和"双引号" |
| 4 | 测试数据4 | 普通数据 |
| 5 | TEST | ABC |
+------+-----------+--------------------------------+
5 rows in set (0.00 sec)

0 0