常用sql语句

删除重复数据

DELETE FROM event WHERE `id`  IN ( 
 SELECT tmp.id FROM 
 (SELECT min(id) as `id` FROM `event` GROUP BY `content_md5` HAVING COUNT(*) >1) AS tmp
)

更新操作数组数据

update news set risk_type = '["产品质量情报", "企业管理情报"]' where risk_type = '["产品质量情报标签", "企业管理情报标签"]';

插入去重更新

1. base_sql: INSERT INTO {} ({}) VALUES({}) ON DUPLICATE KEY UPDATE {};
2. sql 拼接：
    def assemble_sql(table_name, columns):
        """
        TODO: 公共预处理
        :param columns:
        :param table_name:
        :return: sql, columns
        """
        # 删除不需处理字段
        format_columns = ['`' + i + '`' for i in columns]   # 处理mysql中关键字
        str_keys = ",".join([v for v in format_columns])
        str_s = ",".join(['%s'] * (len(columns)))
        # ON DUPLICATE: 去重更新处理
        update_s = ",".join(["{}=VALUES({})".format(v, v) for v in format_columns])
        sql = base_sql.format(table_name, str_keys, str_s, update_s)
        return sql, columns