标签归档:mysql

[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_idname 都是unique 字段

INSERT INTO {{EJS12}} ( {{EJS13}}, {{EJS14}}, {{EJS15}} )
VALUES	(( SELECT id FROM ( SELECT max( tag_id )+ 1 AS id FROM tags ) AS tmp), 'valueA', 'valueB') 
ON DUPLICATE KEY UPDATE {{EJS16}}=VALUES({{EJS17}})

由于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={{EJS23}}@{{EJS24}} FUNCTION {{EJS25}}() RETURNS int
    NO SQL
BEGIN
	SELECT 1+1 into @r;
	return @r;
END

[MYSQL]删除JSON数组中特定数字

这问题折腾了我一下午,首先是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字段中的一个数组以无符号数组读出,并对每个数字进行索引,这样要寻找包含某数字的数组时,将使用索引提速。

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

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

或同时查找多个数字
SELECT * FROM 表名 where JSON_CONTAINS(json字段->'$.指向数组的对象名', CAST('[1,2,3,4,5]' AS JSON));

使用了该索引后,作用在相关字段上的JSON_OVERLAPS 函数也会使用索引。

 

要注意的是,如果数组不是像{"group":[2,4]} 这样对象中的数组,而是字段中直接存放了一个数组[2,4] 的话,索引不起作用,原因不详,但使用explain解析语句时候就是全表查询。

参考: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记录模块还有希望的说喵~~