今天做一个自用小图库的时候发现保存的tag_id没一会就已经上万了，但是实际的tag数量才只有600个左右，找了一会儿发现是`ON DUPLICATE KEY UPDATE` 导致的。

简单来说就是由于mysql在执行语句之前不知道它会进入insert还是update模式，为了一致性只能先取一个自增id，不然如果进了insert模式就没有id用了，就是这样导致即使该语句实际上进入的是update模式也会导致自增id+1。

网上有找到修改`innodb_autoinc_lock_mode`来解决的方法，但是我改成0了之后依然会导致进入update模式后自增+1，不知道为什么。

还有的说在程序里先select，再决定是insert还是update，但这样并发会导致问题，如果select和后续步骤之间有别的请求插入了满足条件的条目，就会导致错误。

方案1

我暂时用了这样一个笨办法，关掉自增，然后手动设置id为最大值+1，其中`tag_id` 和`name` 都是`unique` 字段

INSERT INTO `tags` ( `tag_id`, `name`, `type` ) 
VALUES (( SELECT id FROM ( SELECT max( tag_id )+ 1 AS id FROM tags ) AS tmp), 'valueA', 'valueB') 
ON DUPLICATE KEY UPDATE `type`=VALUES(`type`)

由于insert里不能直接select被insert的表，所以需要在select外面再套一个select，变成两层子查询。

但这样做也有一个很明显的缺陷，因为用的不是数据库自带的自增，于是自然也就没有`LAST_INSERT_ID` ，所以如果需要获取刚刚插入数据的id的话，就得再进行一次查询。

好在可以根据affectedRows判断此语句发生了更新还是插入，如果影响的行数是2的话那就是更新，如果是1就是插入了一条新数据。

注意，这个方法只适用于单个事务依次执行，如果是并发会造成死锁！

方案2

这个问题只在InnoDB会出现，所以如果对并发要求不高的话可以直接改成MyISAM引擎解决问题。

如果有什么别的好办法希望可以给我留言，谢谢。

MySQL中创建函数的时候如果出现了这个错误，表示创建语句中缺少错误中所示的3个函数行为声明，只要根据需求加在Begin前面就可以了。

如果你不确定，那就需要加`DETERMINISTIC` ，如果这个函数不会去操作数据库就写`NO SQL` （不是完全不能有SQL语句），如果需要读取数据就加`READS SQL DATA` 。

比如

CREATE DEFINER=`root`@`%` FUNCTION `poi`() RETURNS int
    NO SQL
BEGIN
	SELECT 1+1 into @r;
	return @r;
END

这问题折腾了我一下午，首先是mysql中没有现成的方法来寻找数组中数字的位置，然后是谷歌百度bing用中文和英文搜索都没找到可用又不烂的办法，爆栈网有一个高票答案，但是似乎并不对，JSON_SEARCH只能用来搜索字符串在json中的位置，数字不支持，不知道他是怎么得到结果的。

经过我晚上的继续摸索，翻遍了官方所有json相关的文档，终于让我拼出了一个方案，就是用`JSON_TABLE` 先把数组转成表，然后用where剔除里面不要的数字，再用`JSON_ARRAYAGG`转回json数组，以下是示例代码
“`sql
set @j='[1,2,5,8,9]’;
select JSON_ARRAYAGG(b) from JSON_TABLE(
@j,
“$[*]”
COLUMNS(
b int PATH “$”
)
) as `破mysql连个数组查找方法都没有`
where b<>5;
“`
这样就可以得到结果[1, 2, 8, 9]，5被删掉了。

这个方法虽然并不优雅，但也没办法，谁叫MySQL连在数组里找数字的位置都做不到呢。

继续阅读[MYSQL]删除JSON数组中特定数字 →

Mysql中在json字段里存放数组时，如果需要快速查找到包含某个数字或字符串的数组，则需要在这个数组上建立索引，其`multi-valued` 索引能满足这种需求。

在已有表的情况下，执行以下语句创建一个数字数组索引：
“`sql
ALTER TABLE 表名 ADD INDEX 索引名((CAST(json字段->’$.指向数组的对象名’ AS UNSIGNED ARRAY)));
“`
这个语句把json字段中的一个数组以无符号数组读出，并对每个数字进行索引，这样要寻找包含某数字的数组时，将使用索引提速。

如果数组内的成员都为字符串，可以这样创建索引（要注意，该表的排序规则需为`utf8mb4_0900_as_cs`，否则会报错）：
“`sql
ALTER TABLE 表名 ADD INDEX 索引名((CAST(json字段->’$.指向数组的对象名’ AS CHAR(255) ARRAY)));
“`

接下来使用类似这样的语句查找拥有某个数字的数组所在的条目
“`sql
SELECT * FROM 表名 where 要找的数字 MEMBER OF(json字段->’$.指向数组的对象名’);
“`
或同时查找多个数字（字符串查找同理）
“`sql
SELECT * FROM 表名 where JSON_CONTAINS(json字段->’$.指向数组的对象名’, CAST(‘[1,2,3,4,5]’ AS JSON));
“`
使用了该索引后，作用在相关字段上的`JSON_OVERLAPS` 函数也会使用索引。

参考：https://dev.mysql.com/doc/refman/8.0/en/create-index.html