MySQL 特色 SQL 语法

REPLACE INTO

REPLACE INTO 与 INSERT INTO 功能类似，当表中已存在相同主键或唯一键的数据时，先删除原有数据再插入新数据；否则直接插入。

mysql> DROP TABLE IF EXISTS TEST;
mysql> CREATE TABLE TEST (id INT PRIMARY KEY, name VARCHAR(20));
mysql> INSERT INTO TEST VALUES(1,'Alice'),(2,'Bob');
mysql> REPLACE INTO TEST VALUES(1,'Charlie');
mysql> REPLACE INTO TEST VALUES(3,'David');
mysql> SELECT * FROM TEST ORDER BY id;
+----+---------+
| id | name    |
+----+---------+
|  1 | Charlie |
|  2 | Bob     |
|  3 | David   |
+----+---------+

INSERT IGNORE

当插入的数据存在主键或唯一键冲突时，使用 INSERT IGNORE 可以忽略冲突，插入不冲突的数据。

mysql> CREATE TABLE t1_ignore (c1 INT PRIMARY KEY, c2 VARCHAR(50));
mysql> INSERT INTO t1_ignore VALUES(1,'aaa'),(2,'bbb'),(3,'ccc');
mysql> INSERT IGNORE INTO t1_ignore VALUES(1,'aaa'),(4,'ddd');
mysql> SELECT * FROM t1_ignore ORDER BY c1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
|  4 | ddd  |
+----+------+
mysql> DROP TABLE t1_ignore;

INSERT ON DUPLICATE KEY UPDATE

当插入的数据存在主键或唯一键冲突时，使用 ON DUPLICATE KEY UPDATE 可以更新已有记录。

mysql> DROP TABLE IF EXISTS test1;
mysql> CREATE TABLE test1 (id INT PRIMARY KEY, name VARCHAR(50));
mysql> INSERT INTO test1 VALUES(1,'John'),(2,'Jane');
mysql> INSERT INTO test1 VALUES(1,'John') ON DUPLICATE KEY UPDATE name='John Updated';
mysql> SELECT * FROM test1 ORDER BY id;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | John Updated |
|  2 | Jane         |
+----+--------------+
mysql> DROP TABLE test1;

INSERT INTO ... SET 语法

MySQL 支持使用 SET 子句替代 VALUES 子句进行插入。

mysql> DROP TABLE IF EXISTS tab1;
mysql> CREATE TABLE tab1 (id INT, name1 VARCHAR(128), name2 VARCHAR(128), UNIQUE(name1));
mysql> INSERT INTO tab1 SET name1='n1', id=1, name2='n2';
mysql> SELECT * FROM tab1;
+------+-------+-------+
| id   | name1 | name2 |
+------+-------+-------+
|    1 | n1    | n2    |
+------+-------+-------+
mysql> DROP TABLE tab1;

LIMIT 分页

LIMIT 用于限制查询结果返回的行数，支持 LIMIT N、LIMIT N,M 和 LIMIT N OFFSET M 三种语法。

mysql> CREATE TABLE TEST (id INT PRIMARY KEY, val INT);
mysql> INSERT INTO TEST VALUES(1,10),(2,20),(3,30),(4,40),(5,50);
mysql> SELECT * FROM TEST LIMIT 3;
+----+-----+
| id | val |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
+----+-----+
mysql> SELECT * FROM TEST LIMIT 2,2;
+----+-----+
| id | val |
+----+-----+
|  3 |  30 |
|  4 |  40 |
+----+-----+
mysql> DROP TABLE TEST;

PREPARE 预处理

MySQL 支持 PREPARE / EXECUTE / DEALLOCATE 预处理语句。

mysql> PREPARE stmt1 FROM 'SELECT ? + ?';
mysql> SET @a=1, @b=2;
mysql> EXECUTE stmt1 USING @a, @b;
+----------+
| ?column? |
+----------+
|        3 |
+----------+
mysql> DEALLOCATE PREPARE stmt1;

MySQL 用户变量

MySQL 支持使用 SET @var = value 定义用户变量。

mysql> SET @myvar = 100;
mysql> SELECT @myvar;
+--------+
| @myvar |
+--------+
|    100 |
+--------+