官网

mysql insert
语法

  1. INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
  2.     [INTO] tbl_name
  3.     [PARTITION (partition_name [, partition_name] ...)]
  4.     [(col_name [, col_name] ...)]
  5.     { {VALUES | VALUE} (value_list) [, (value_list)] ...
  6.       |
  7.       VALUES row_constructor_list
  8.     }
  9.     [AS row_alias[(col_alias [, col_alias] ...)]]
  10.     [ON DUPLICATE KEY UPDATE assignment_list]
  12. INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
  13.     [INTO] tbl_name
  14.     [PARTITION (partition_name [, partition_name] ...)]
  15.     [AS row_alias[(col_alias [, col_alias] ...)]]
  16.     SET assignment_list
  17.     [ON DUPLICATE KEY UPDATE assignment_list]
  19. INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
  20.     [INTO] tbl_name
  21.     [PARTITION (partition_name [, partition_name] ...)]
  22.     [(col_name [, col_name] ...)]
  23.     [AS row_alias[(col_alias [, col_alias] ...)]]
  24.     {SELECT ... | TABLE table_name}
  25.     [ON DUPLICATE KEY UPDATE assignment_list]
  27. value:
  28.     {expr | DEFAULT}
  30. value_list:
  31.     value [, value] ...
  33. row_constructor_list:
  34.     ROW(value_list)[, ROW(value_list)][, ...]
  36. assignment:
  37.     col_name = [row_alias.]value
  39. assignment_list:
  40.     assignment [, assignment] ...

小试牛刀——简单语句

  1. -- 例子表
  2. CREATE TABLE `test_tab` (
  3.   `id` int NOT NULL AUTO_INCREMENT,
  4.   `name` varchar(64) DEFAULT NULL,
  5.   `gender` varchar(2) DEFAULT NULL,
  6.   `salary` double DEFAULT NULL,
  7.   `birthday` datetime DEFAULT NULL,
  8.   PRIMARY KEY (`id`)
  9. ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  11. -- 例子语句
  12. INSERT INTO test_tab (
  13.     id,
  14.     NAME,
  15.     gender,
  16.     salary,
  17.     birthday
  18. )
  19. VALUES
  20.     (
  21.         1,
  22.         '小辉',
  23.         '男',
  24.         1200,
  25.         '2021-01-21 10:09:01'
  26.     ),
  27.     (
  28.         2,
  29.         '小辉',
  30.         '男',
  31.         1010,
  32.         '2021-01-21 10:09:01'
  33.     ) AS new ON DUPLICATE KEY UPDATE salary = new.salary,
  34.     NAME = new. NAME,
  35.     gender = new.gender;

小结

  1. insert into 语句可以实现 saveOrUpdate 功能。
  2. insert into 可以使用 set 语句。 ```sql INSERT INTO t1 SET a=1,b=2,c=3 AS new ON DUPLICATE KEY UPDATE c = new.a+new.b;

INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p) ON DUPLICATE KEY UPDATE c = m+n; ```

  1. insert into 返回的影响行数，可以不等于插入的条数；（原因未探😂）。
  2. insert into 没有 where 子句。