标签归档：mysql

笔记

[MySQL]JSON数组取对称差集函数

留下评论

写了个函数，做个记录。作用是扣掉两个数组相同的部分，只保留不同的部分，也就是对称差集。

CREATE FUNCTION `array_diff`(`arr1` json, `arr2` json) RETURNS json
    NO SQL
    DETERMINISTIC
BEGIN
	RETURN (
		WITH 
		A AS (SELECT el FROM JSON_TABLE(arr1, '$[*]' COLUMNS(el INT PATH '$')) T),
		B AS (SELECT el FROM JSON_TABLE(arr2, '$[*]' COLUMNS(el INT PATH '$')) T)
		SELECT JSON_ARRAYAGG(el) FROM (
			SELECT el FROM A WHERE A.el NOT IN (SELECT el FROM B)
			UNION
			SELECT el FROM B WHERE B.el NOT IN (SELECT el FROM A)
		) AS T
	);
END

调用

select array_diff('[1,2,3]','[2,3,4]')

结果

[1, 4]

 

佳佳教程

[MySQL]在一个update语句中对不同的行更新不同的数据

留下评论

mysql中update的基础用法是update 表 set 列1=?,列2=?,... where 筛选条件，这样的语句一次只可以对符合条件的所有行更新相同的数据，但update语句还可以join其它的表，从别的表读取数据更新到对应的行上，这样就可以实现对不同的行更新不同的数据，如下：

update
	表名 as t1
	inner join 表2 as t2      #也可以left join 或 right join 或 join，这里的join和select里面那个join用法是一样的
	on t1.id列名=t2.id列名
set
	t1.列1 = t2.列1,
	t1.列2 = t2.列2

虽然上面的方法能实现标题的要求，但总不能先把数据插入一个表再这样update吧，于是我想了一会儿，想到如果要提交多组数据肯定要把数据结构化，如果要提交结构化数据那肯定是要用json，mysql 的json函数里有一个`JSON_TABLE`函数可以用来把一个json转换成一个表类型，那么这个方法就好实现了，只要把上面的表2替换成用JSON_TABLE生成的表，就可以通过直接向mysql提供一个json来对不同的行更新不同的数据了，如下：

update
	表名 as t1
	inner join JSON_TABLE(
		?,
		'$[*]' COLUMNS (    #把json作为一个数组遍历，提取每个子数组作为数据源
			id列名 BIGINT PATH '$[0]',    #提取子数组第一个元素，取名为"id列名"，类型是BIGINT，建议取名为目标表中的id列名，这样方便用using
			列1 INT PATH '$[1]',          #提取子数组第二个元素，取名为"列1"，类型是INT
			列2 VARCHAR PATH '$[2]'       #提取子数组第三个元素，取名为"列2"，类型是VARCHAR
		)
	) as t2 using(id列名)       #这个using是前一个例子中on语法的一种简写，用法参考官方文档
set
	t1.列1 = t2.列1,
	t1.列2 = t2.列2;

在 ? 的位置填入一个json字符串或者prepare这个语句后提交一个json字符串，就可以啦。json字符串示例如下：

[
	[1,20,"张三"],
	[2,24,"李四"],
	[3,18,"王五"],
	[4,23,"赵六"],
	[5,55,"小二"],
	[6,60,"poi"]
]

这样每个子数组中的第一个项目被提取为”id列名”，第二个为”列1″，第三个为”列2″（都是在上面的sql里的JSON_TABLE里取的名字），对应的值在set语句里被赋值到对应的行上，功能就完成啦。

佳佳教程

[MySQL]在普通字符串字段中存放json数组并索引

留下评论

之前有一篇文章写的是在mysql中使用json字段时为其中的数组创建索引，它有个问题就是当该字段本身存放的就是一个数组时（不放在对象下的属性中），这样创建的索引不生效，原因不明。因此要使索引生效需要把数组放在对象下的一个属性里。

现在我发现，如果想要在mysql中存放一个可以索引的json数组，其实根本不需要使用json字段，即使是普通的字符串字段存放的json数组也可以用同样的方法进行索引。

比如现在有一个字段`tags`，类型为`mediumtext`，在其中一行存放了以下字符串:

[1,2,3,4,5,6,7,8,9,10]

然后将其解析为json来创建多值索引：

ALTER TABLE 表 ADD INDEX tag_idx((cast(JSON_EXTRACT(tags, '$') as unsigned array)))

接着在查询数据时也把该字段解析为json进行使用：

SELECT *
FROM 表
WHERE JSON_OVERLAPS('[1,2]',JSON_EXTRACT(tags, '$'))

在这样的查询中，mysql就会使用到这个json数组的索引，因此如果一个数据仅仅是为了作为数组使用的话就没必要套个对象放进json字段了，使用普通字符串字段即可。

佳佳教程

[MySQL]member of 不走索引

留下评论

刚刚发现程序里有一个语句，是一个unique主键 member of(返回json数组的子查询)，但它就是不走主键索引，接着我把子查询换成了一个手写的json串，像这样

SELECT
    tag_id
FROM
    tags FORCE INDEX ( tag_id_unique ) 
WHERE
    tag_id member of (JSON_EXTRACT( '[1,2,3]', '$' ))

发现即使是指定了索引也还是不走索引，可能member of就是这样的机制？ 目前原因还不清楚，于是我先用了另一个办法。

继续阅读[MySQL]member of 不走索引 →

悠哉博主的日常

MySQL mbind: Operation not permitted

留下评论

刚刚mysql启动不起来了，翻了翻docker日志，里面全是`mbind: Operation not permitted` ，然后netstat看了看也没别的程序占用端口，怎么就没权限监听端口了呢。

咕咕查了查，都是说和docker-compose有关的答案，但是我没用它，只好再研究是什么原因。

直到我编辑了一个文件然后保存的时候终于知道是什么问题了，它报了个硬盘空间不足，估计又是什么log把硬盘撑爆了，然后就du命令一路找，找到了一个9个多G的docker容器log，删掉它之后重启docker，mysql也可以启动起来了。

为了防止问题再发生，给docker容器设置了个log大小限制。

悠哉博主的日常

INSERT ON DUPLICATE KEY UPDATE导致自增字段不连续

留下评论

今天做一个自用小图库的时候发现保存的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]This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

留下评论

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]删除JSON数组中特定数字

2条评论

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

经过我晚上的继续摸索，翻遍了官方所有json相关的文档，终于让我拼出了一个方案，就是用`JSON_TABLE` 先把数组转成表，然后用where剔除里面不要的数字，再用`JSON_ARRAYAGG`转回json数组，以下是示例代码

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字段中的数组创建索引

留下评论

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

在已有表的情况下，执行以下语句创建一个数字数组索引：

ALTER TABLE 表名 ADD INDEX 索引名((CAST(json字段->'$.指向数组的对象名' AS UNSIGNED ARRAY)));

这个语句把json字段中的一个数组以无符号数组读出，并对每个数字进行索引，这样要寻找包含某数字的数组时，将使用索引提速。

如果数组内的成员都为字符串，可以这样创建索引（要注意，该表的排序规则需为`utf8mb4_0900_as_cs`，否则会报错）：

ALTER TABLE 表名 ADD INDEX 索引名((CAST(json字段->'$.指向数组的对象名' AS CHAR(255) ARRAY)));

接下来使用类似这样的语句查找拥有某个数字的数组所在的条目

SELECT * FROM 表名 where 要找的数字 MEMBER OF(json字段->'$.指向数组的对象名');

或同时查找多个数字（字符串查找同理）

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

神奇的问题

mysql的蛋疼注意事项一条

留下评论

今天一个神一般的问题纠结了我一天。。
那就是Mysql创建table的时候，语法问题。

本来是想做个ip记录模块的，结果时间都浪费在这里了。接下来是高能重点！！！！

$info = "CREATE TABLE ips
(
number int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(number),
ip varchar(30)
)";
mysql_query($info,$sql);

上面这是修改后正确的代码（其中一个原来出错的部分）,先来猜猜原来是哪里出错了呢~~~~

 

 

 

 

希望这是大多数人都遇到过的问题，接下来公布答案。。

那就是ip varchar(30)这个了，不得不说mysql解析的智能程度还不够，我只是在最后加了个,而已，它就不认爹了【也就是ip varchar(30),】

接下来是错误版

$info = "CREATE TABLE ips
(
number int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(number),
ip varchar(30),   //←注意这个悲催的逗号，难道逗号后面没有参数它就不能当作空参数吗！执行到这里就直接当作错误跳过了【坑爹呐！！！（掀桌子）】
)";
mysql_query($info,$sql);

值得庆幸的是。。我终于发现并解决了这个问题。ip记录模块还有希望的说喵~~