多条语句一起执行,MySQL报错SQL syntax error的问题探究

  昨晚上升级了阿里云的MySQL到独享代理,然后对表也进行了一系列的操作,由于是业务低谷期,测试网站使用没问题就睡了。
  但是今天一大早就接到电话,统计服务崩了。赶紧起来修复,发现一直运行的程序,居然报错了,而且报错内容是:1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create_tim' at line 1.
  接下来,就是找bug,解决问题的时候了。

虽然如此,但是我仍然
????

之前都好好的,咋就突然语法错误了呢,我啥都没动它啊,甚至重启程序都没有。怎么会这样呢。

然而不管怎么疑惑,问题还是要解决的。

定位问题

我复制sql下来,用我的Navicat执行,执行成功。
代码复制回来,在我的电脑上跑脚本,一样报错。
所以是代码有问题。

  • 把连接在一起的SQL语句,分开成一行一个,用\n隔开,发现报错的地方在line 2的中间了。
  • 在Navicat把第一行的语句执行了,然后删除第一行语句,再次执行。
    报错地方还是在line 2
  • 把多余的语句删除掉,只留一行,执行,居然通过了。看来很接近真相了。

推测:是多条语句一起执行导致的问题,应该和升级了mysql代理有关系。


解决方案1

既然是多条SQL一起执行的问题,那就改成一条吧。把pymysqlexcute(sql),改成excutemany(sql, data)

  • 优点:插入速度变快(几乎不太能感受到,因为我一次执行最多也就几十条)。
  • 缺点:不能拿来执行update语句。
    搜到的教程有使用update的,然而由于我的update语句是通过where id=123来限制条件,使用excutemany会变成where id='123'。也就是整数123,变成了字符串123。SQL语句在执行的时候存在隐式类型转换,执行时间和CPU占用大大提高(根据经验,甚至会全表扫描),原本10秒执行完毕的语句,需要200秒才能够基本上执行完毕,严重拖慢了速度,影响了mysql的正常使用。

由于优点对我来说,感知不强。但是缺点对我来说却是致命的。经过查询,此时可以使用case when进行update的组合,但是由于我更新的字段较多,也不太适用于case when。

于是问题又回来了,怎么解决不能一次性执行多条语句的问题,要从pymysql入手才行。
找度娘,都是php,java的。不过java的有关文章给了我启示,java解决这种问题是通过连接MySQL时,添加连接参数:allowMultiQueries=true,如:hikariConfig.security.jdbcUrl=jdbc:mysql://xx.xx.xx:3306/xxxxx?characterEncoding=utf-8&allowMultiQueries=true解决的。

可是我找pymysql的连接参数和文档,没有发现在链接上加参数allowMultiQueries=true的途径,这次度娘也无法帮助我了。


谷歌的强大之处就体现出来了,Stack Overflow是个好网站。经过长久的搜索,终于找到了我想要的答案,也是最完美的答案,不需要对pymysql源代码进行修改,对业务代码的改动也是最小的。

解决方案2

先上链接:# Python-PyMySQL unable to execute multiple queries Stack Overflow

GitHub pyMySQL changelog: PyMySQL/CHANGELOG at v0.9.3 · PyMySQL/PyMySQL

import pymysql
from pymysql.constants import CLIENT

conn = {
    "host": "mysql_server_hostname",
    "password": "my_password",
    "port": <<port_no>>,
    "user": "my_username",
    "client_flag": CLIENT.MULTI_STATEMENTS
}

my_query_str = 'show databases;use random_existing_db;'
with pymysql.connect(**conn) as cur:
    cur.execute(my_query_str)

所以只是需要from pymysql.constants import CLIENT,并且在connect的时候,加上参数client_flag=CLIENT.MULTI_STATEMENTS就好了。

        try:
            db_session = pymysql.connect(host, username, pwd, db_name, 
                                         client_flag=CLIENT.MULTI_STATEMENTS)
            cursor = db_session.cursor()
        except Exception as e:
            print('连接数据库失败:{}'.format(e))

事情圆满解决,又学到了新的知识。