MySQL5.6不支持SET OPTION的写法

迈不过友情╰ 2022-09-18 06:57 63阅读 0赞

**MySQL5.6不支持SET OPTION的写法**

MySQL SET语法用来设置变量，  
如：set AUTOCOMMIT =1; 或 OPTION AUTOCOMMIT =1;

在5.1和5.5的官方手册里写了不推荐使用后面这种SET OPTION的写法。不过在5.6版本里变成不支持SET OPTION的写法，5.6用例如下：

Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 16810290
    Server version: 5.6.11-log Source distribution
    
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    
    mysql> set option AUTOCOMMIT =1;
    ERROR 1064 (42000): 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 'option AUTOCOMMIT =1' at line 1
    mysql>

由于5.6这个变化，导致一些旧的软件可能运行出错。mysql官方也不承认这是bug，所以只能升级你的软件或驱动。  
  
在5.1.12的jdbc的客户端版本里就遇到一个问题  
当用jdbc Statement调用setMaxRows()方法后，statement的close()方法会报错。

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1
            at sun.reflect.GeneratedConstructorAccessor193.newInstance(Unknown Source)
            at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
            at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
            at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
            at com.mysql.jdbc.Util.getInstance(Util.java:386)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
            at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
            at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
            at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
            at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1595)
            at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:794)
            at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:656)

测试升级到5.1.22的驱动即没问题。