mysql 索引、去重插入、存储带引号json、高级sql语句的一些经验

记录一下工作中，关于mysql的经验，不定期更新trips

一、python 处理数据库中 带引号的 json数据

前提

1、数据库字段类型为 string（longtext）
2、json存入mysql，一般先json.dumps(data) 进行序列化。读取mysql中的json数据时，json.loads(data)方式反序列化。
3、json数据的键值以双引号的方式存储。如：“abs”。

text["abs"] = "xxxxxx"
{
  "abs": "xxxxxxx"
}

读取

python 读取当前字段并以json.loads的方式加载为json格式数据。

# mysql中的数据,双引号前有一个转义字符
{"abs":"有效提高\"地质\"分析的水平"}

# python select后的数据，双引号前两个转义字符。（第一个转义字符修饰第二个，第二个转义字符不转义？） 
'{"abs":"有效提高\\"地质\\"分析的水平"}'

# 执行loads操作后，数据中没有转义字符， {"abs":"有效提高"地质"分析的水平"}
data =  json.loads(json_message)

写入

当json数据中有双引号时，写入到mysql中的数据需要带有一个转义字符，否则，下次读取操作时，json.loads(data)将出错。
对数据操作后，写入mysql时，需要将双转义 替换为四转义，（分别代表python转义和sql语句的转义吗？） 最终写入mysql时，数据中只有一个转义字符。

# 原始json格式数据：{"abs":"有效提高"地质"分析的水平"}

# 执行序列化操作后的数据格式， '{"abs":"有效提高\\"地质\\"分析的水平"}'
content = json.dumps(dir.get('content'), ensure_ascii=False)

# 很关键的一步！！！将两个转义字符替换为4个。可以保证下次读取mysql时，json.loads不出错
content = content.replace('\\',"\\\\")
'{"abs":"有效提高\\\\"地质\\\\"分析的水平"}'
# 写入mysql,mysql中的内容：
{"abs":"有效提高\"地质\"分析的水平"}

# 如果不进行上一步的replace，mysql中的格式为：{"abs":"有效提高"地质"分析的水平"}，此时，再进行json.loads(data)将出错

上述方式读取和写入时，数据库中字符串形式的json（带双引号）数据可以被正确加载为python的json格式

c++中，读取mysql数据时，如果数据库中双引号带有一个转义字符，jsonlib库解析后的数据中的双引号之前也带有转义字符

上述python和c++的现象的本质原因是什么？有点懵，当前记录一下正确的处理步骤，有时间好好探究一下，如果哪位朋友明白原理的，请评论区指教

二、唯一索引

唯一索引可以作用于组合字段。当表中除了主键外，其余字段无法单独设置唯一索引时，组合字段联合设置唯一索引的方式非常方便。

# 查看表中是否有唯一索引
show INDEX FROM tablenamexxx

# 设置唯一索引，作用于单字段
alter table tablenamexxx add unique index(fieldxx);
# 设置唯一索引，作用于组合字段
alter table tablenamexxx  add unique index(fieldxx1,fieldxx2);

# 删除唯一索引
alter table tablenamexxx drop index fieldxx;

三、一些复杂sql语句

1. insert if exist update

插入数据时，如果表中存在该数据则更新，不存在则插入的优雅处理方式：

# update后更新的字段可以是多个，至少一个
INSERT into tablenamexxx(UserID, ResID, Guid, factor) VALUES ("030f09f3-c41a-4bd0-9dfc-0cf6ab7e9552", "SKDM", "1016366648169050112", 3) 
ON DUPLICATE KEY UPDATE factor= VALUES(factor)

2. select 查询排序

满足以下业务需求：根据某字段factor排序，同时不打乱factor相同时，数据原本的顺序。选取另一字段作为factor外的排序准则，比如ID。

SELECT * FROM prk_userdatasamples_20221013
WHERE UserID='030f09f3-c41a-4bd0-9dfc-0cf6ab7e9552'
ORDER BY factor DESC, ID

3. 根据字段聚合（去重），并分组计数

SELECT UserID, count(*) as count
FROM prk_userdatasamples_20230111
GROUP BY UserID

4. 关联查询，not in 表2

查询prs_userprofile表中的userid 哪些不在表prk_userdatasamples_20230111 中

SELECT prs_userprofile.UserID 
FROM prs_userprofile
LEFT JOIN prk_userdatasamples_20230111 ON prs_userprofile.UserID=prk_userdatasamples_20230111.UserID
WHERE 
1=1 AND prk_userdatasamples_20230111.UserID IS NULL

5. 关联查询，子查询结果作为where in 的条件

以 4查询结果作为where in 的条件，查询prs_userprofile表中，满足userid条件的所有信息

SELECT * FROM prs_userprofile
WHERE UserID in
(
SELECT prs_userprofile.UserID 
FROM prs_userprofile
LEFT JOIN prk_userdatasamples_20230111 ON prs_userprofile.UserID=prk_userdatasamples_20230111.UserID
WHERE 1=1
AND prk_userdatasamples_20230111.UserID IS NULL
)

6. 子查询结果作为update的条件

mysql中，update直接使用子查询结果，会报如下错误：
1093 - You can’t specify target table ‘prs_userprofile’ for update in FROM clause
此时，需要再添加一层子查询

UPDATE prs_userprofile
set Recall=10
WHERE UserID in
(
SELECT ub.UserID FROM
	(
		SELECT prs_userprofile.UserID 
		FROM prs_userprofile
		LEFT JOIN prk_userdatasamples_20230111 ON prs_userprofile.UserID=prk_userdatasamples_20230111.UserID
		WHERE 1=1
		AND prk_userdatasamples_20230111.UserID IS NULL
	)ub
)