Mysql实战中的一些小tips

1. 问题：使用select进行查表，出现连接事务一直连接，导致锁表，没办法对表格进行修改操作。

        解决办法：

# 在建立conn连接时，设置commit如下：

self.dbconn = MySQLdb.connect(host=sql_conf.MYSQL_HOST, port=sql_conf.MYSQL_PORT, user=sql_conf.MYSQL_USER, passwd=sql_conf.MYSQL_PASS, db=sql_conf.MYSQL_DB, connect_timeout=10)

self.dbconn.autocommit(True)

2. 从数据库读取json.dumps的字段后，直接使用json.loads()转换，出现错误：Expecting ',' delimiter: line 1 column 7 (char 6)

错误原因是，字符串格式不规范，如双引号里面含有双引号：

'["1.0"大尺寸CMOS 约2020万有效像素", "F1.8-2.8大光圈，24mm广角+4.2倍光学变焦镜头，5cm微距", "Wi-Fi影像分享，NFC近场通信，更方便的遥控拍摄", "汇集多种模式，丰富你的拍摄构思", "机身搭载取景器，更专业与更舒适的拍摄体验”]'

解决方法：

在入库之前，使用pymysql.escape_string(value)对dumps后的字符串先进行下转换，变成数据库格式字符串，再入库即可。这个操作，会对双引号内部的双引号进行转义。

3. 错误：Incorrect string value: '\xE4\xB8\xAD\xE5\x9B\xBD' for column 'word' at row 1

排查步骤：

1. 排查数据库编码，确认是否是 utf-8
2. 如果还有问题，继续确认表格编码是否是utf-8
3. 如果还有问题，继续确认表格中字段编码是否是utf-8

本次遇到的问题，是因为字段的编码是char，调整为utf-8格式即正常。

4. 需求场景为：有表格a，表格b，和表格c，

对于c中满足a.user_id=c.user_id的记录，更新a中对应记录的某些字段：

UPDATE c
SET 
    (c.USER_NAME, c.ID_CARD, c.MOBILE) = (
        SELECT a.USER_NAME, a.CARD_NO, a.MOBILE
        FROM a
        WHERE a.USER_ID = c.USER_ID
    )
WHERE EXISTS (
    SELECT 1
    FROM a
    WHERE c.USER_ID = a.USER_ID
);

对于上面的查询需求，如果where条件过滤后得到的条数不只1条，导致更新出错时，如需求上可以过滤掉多数的轻轻，可以做如下处理：

UPDATE c
SET 
    (c.USER_NAME, c.ID_CARD, c.MOBILE) = (
        SELECT uni_a.USER_NAME, uni_a.CARD_NO, uni_a.MOBILE
        FROM (
            SELECT a.USER_NAME,
                   a.CARD_NO, 
                   a.MOBILE, 
                   ROW_NUMBER() OVER (PARTITION BY a.USER_ID) AS rn
            FROM a
        ) uni_a
        WHERE a.USER_ID = c.USER_ID
        AND uni_a.rn = 1
    )
WHERE EXISTS (
    SELECT 1
    FROM a
    WHERE c.USER_ID = a.USER_ID
);