使用presto mysql驱动查询表

🪐 TiDB 技术问题 部署&运维管理
1

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

  • 【TiDB 版本】：tidb4.07
  • 【问题描述】：使用presto mysql驱动查询表 出错，
    这边在mysql 和tidb 都创建了同样都一张表，然后使用presto 去查询 发现查询mysql没有问题，但是查询tidb会出错，

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

2

如下是建表语句
CREATE TABLE receiving (
receiving_id int(11) NOT NULL,
expected_date date NOT NULL DEFAULT ‘0000-00-00’,
UNIQUE KEY udx_receiving_id (receiving_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3

tidb查询结果

image
image1668×464 54.3 KB

mysql查询结果

image
image1480×660 51.5 KB

4

里面都数据如下

image
image724×278 9.93 KB

5 
mysql> CREATE TABLE receiving ( receiving_id int(11) NOT NULL, expected_date date NOT NULL DEFAULT '0000-00-00', UNIQUE KEY udx_receiving_id (receiving_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ERROR 1067 (42000): Invalid default value for 'expected_date'

手动执行这个 SQL 报错
可能需要设置 sql_mode
https://docs.pingcap.com/tidb/dev/sql-mode

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> set @@sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 54 near ",NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 
mysql> set @@sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE receiving ( receiving_id int(11) NOT NULL, expected_date date NOT NULL DEFAULT '0000-00-00', UNIQUE KEY udx_receiving_id (receiving_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected (0.01 sec)

把 NO_ZERO_DATE 去掉