When using the `long byte` type to create a table, TiDB and MySQL behave inconsistently

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 建表使用 long byte 类型时，TiDB 与 MySQL 表现不一致

| username: ohooono1

【TiDB Environment】Test environment
【TiDB Version】v6.1.0
【Encountered Problem】

Create table statement:
CREATE TABLE t1 (col2 LONG BYTE) ENGINE=INNODB CHARACTER SET UTF8MB4;

  • TiDB table structure is as follows
mysql> SHOW CREATE TABLE t1;
+-------+------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `col2` mediumtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  • MySQL 5.7.36 table structure is as follows
mysql> show create table t1;
+-------+---------------------------------------------------------------------------------+
| Table | Create Table                                                                    |
+-------+---------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `col2` mediumblob
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

This inconsistency causes the pymysql/tests/thirdparty/test_MySQLdb/test_MySQLdb_capabilities.py::test_MySQLdb::test_LONG_BYTE test case to fail.

2
| username: ddhe9527 | Original post link

In MySQL, LONG is an alias for MEDIUMTEXT, and CHAR BYTE is an alias for BINARY. However, I couldn’t find documentation for LONG BYTE, and I haven’t used it before. Based on test results, it seems to be an alias for MEDIUMBLOB. The “BYTE” keyword turns TEXT into BLOB. It appears that TiDB does not support this usage.

3
| username: ohooono1 | Original post link

From a semantic perspective, MEDUIMBLOB seems more reasonable. I hope TiDB’s behavior can be corrected, or it can be specifically explained in the compatibility documentation.

4
| username: system | Original post link

This topic was automatically closed 1 minute after the last reply. No new replies are allowed.