MYSQL 存在则更新,否则新增

Question

1.MYSQL 存在则更新,否则新增
2.新增时,No是当前最大的编号+1
3.以下写法报错，请纠正

INSERT INTO tb(Name,No, Host,IP) VALUES ('m1',SELECT MAX(NO)DevNO FROM tb, 'Alice', '1934')
ON DUPLICATE KEY UPDATE No = 50;

Answer 1

引自免费微信小程序：皆我百晓生

你的需求是在MySQL中执行一个操作，如果该记录已存在则更新，否则新增，并且在新增时，No是当前最大的编号+1。根据你提供的SQL语句，我可以帮你纠正并改进。

首先，你的SQL语句中有一些错误和不清晰的地方。以下是逐步的纠正和解释：

1. 在 VALUES 中的 SELECT MAX(NO)DevNO FROM tb 部分应该被放在子查询中，因为你不能直接在一个 VALUES 语句中使用 SELECT 语句。此外，你提到了 DevNO，但在后续的语句中使用了 No，请确保字段名称是一致的。
2. 使用 ON DUPLICATE KEY UPDATE 时，你提到了 No = 50，这是固定的值。但根据你的需求，应该使用新的 No 值，即当前的最大编号+1。因此，你不能直接设置一个固定的值。此外，如果 Name 或 Host 字段也是唯一索引的一部分（或主键），则 ON DUPLICATE KEY UPDATE 会根据这些字段的匹配来工作。
3. 为了确保新记录的 No 是当前最大编号+1，你可以使用 AUTO_INCREMENT 属性来自动为新记录分配编号。但这需要你更改表结构。或者你可以使用一个简单的子查询来获取最大编号并加1。但请确保在并发环境下考虑线程安全性。

考虑到以上所有点，以下是一个可能的解决方案：

假设你的表结构允许这样做，你可以考虑以下步骤：

1. 修改表结构以使用 AUTO_INCREMENT 属性（如果适用）。这将自动为新记录分配编号。但如果你要手动控制编号，可以继续下面的步骤。
2. 使用一个事务来确保操作的原子性。这样可以避免在并发环境下出现问题。

SQL语句如下：

START TRANSACTION;
SET @newNo = (SELECT COALESCE(MAX(No), 0) + 1 FROM tb); -- 获取最大编号+1
INSERT INTO tb(Name, No, Host, IP) 
VALUES ('m1', @newNo, 'Alice', '1934')
ON DUPLICATE KEY UPDATE No = @newNo; -- 如果已存在则更新No值
COMMIT; -- 提交事务

注意事项：上述代码中的 COALESCE(MAX(No), 0) 用于处理当表中没有记录时的情形，此时最大编号将是NULL，因此使用COALESCE将其转换为0然后加1。在真实场景中，你可能需要根据你的业务逻辑进行调整。此外，在高并发环境下使用这种方法可能需要考虑性能问题和其他可能的并发问题。