MySQL使用技巧总结

JSON 数据的使用

首先有一张 test 表，content 字段保存的是 JSON 数据。

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

插入几条测试数据

INSERT INTO test
VALUES
	( NULL, '标题1', '{ "key1" : "内容1", "key2" : "内容1", "123" : "内容1" }' ),
	( NULL, '标题2', '{ "key1" : "内容2", "key2" : "内容2", "123" : "内容2" }' ),
	( NULL, '标题3', '{ "key1" : "内容3", "key2" : "内容3", "123" : "内容3" }' );

查询字段 key1 为“内容1”的数据，这里使用JSON_EXTRACT()，更多点击：MySQL 官方文档

SELECT * FROM test WHERE JSON_EXTRACT(content,'$.key1')='内容1';

查询字段为“123”的内容为“内容1”的数据

因为字段为纯数字，所以需要加双引号("")

SELECT * FROM test WHERE JSON_EXTRACT(content,'$."123"')='内容1';

为 JSON 内部字段 key1 加索引
第一步：添加虚拟字段

ALTER TABLE test ADD COLUMN `key1_virtual` VARCHAR (20) GENERATED ALWAYS AS (json_unquote(json_extract(content,'$.key1'))) VIRTUAL;

第二步：创建索引

ALTER TABLE test ADD INDEX `idx_key1`(key1_virtual) USING BTREE;

测试

EXPLAIN SELECT * from test where key1_virtual='内容1';

结果如下，idx_key1 索引生效了

+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_key1      | idx_key1 | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)

另外还有一种添加索引的方法：查看

---

参考文章：
https://developer.aliyun.com/article/303208

mysql更新 A 表的字段等于 B 表的字段

UPDATE 
	article a1,
	article a2 
SET a1.name1 = a2.name 
WHERE
	a1.id = a2.id 
	AND a1.name1 = '' 
	AND a2.name1 = '';