Add CHECK_CONSTRAINTS table for mysql8 compatibility

[Mini256]

Feature Request

Is your feature request related to a problem? Please describe:

After PR #46155 merged into master, the change in MySQL version number caused the Prisma's integration tests continuously report errors.

The failure reason is because Prisma found that CHECK_CONSTRAINTS table does not exist. I guess because Prisma decides whether to read the table based on the version number of mysql.

Describe the feature you'd like:

Add the CHECK_CONSTRAINTS meta-information table before a new version is released, otherwise this will prevent Prisma users from using the latest version of TiDB properly:

https://dev.mysql.com/doc/mysql-infoschema-excerpt/8.0/en/information-schema-check-constraints-table.html

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[dveeden]

MySQL

sql> CREATE TABLE t1 (id int primary key, check (id>10));
Query OK, 0 rows affected (0.0739 sec)

sql> DESCRIBE information_schema.CHECK_CONSTRAINTS;
+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(64) | NO   |     | NULL    |       |
| CONSTRAINT_SCHEMA  | varchar(64) | NO   |     | NULL    |       |
| CONSTRAINT_NAME    | varchar(64) | NO   |     | NULL    |       |
| CHECK_CLAUSE       | longtext    | NO   |     | NULL    |       |
+--------------------+-------------+------+-----+---------+-------+
4 rows in set (0.0033 sec)

sql> TABLE information_schema.CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+--------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE |
+--------------------+-------------------+-----------------+--------------+
| def                | test              | t1_chk_1        | (`id` > 10)  |
+--------------------+-------------------+-----------------+--------------+
1 row in set (0.0017 sec)

sql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.1.0     |
+-----------+
1 row in set (0.0006 sec)

TiDB

sql> SET GLOBAL tidb_enable_check_constraint=ON;
Query OK, 0 rows affected (0.0459 sec)

sql> CREATE TABLE t1 (id int primary key, check (id>10));
Query OK, 0 rows affected (0.2051 sec)

sql> DESCRIBE information_schema.CHECK_CONSTRAINTS;
ERROR: 1146 (42S02): Table 'information_schema.CHECK_CONSTRAINTS' doesn't exist

sql> SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v7.3.0
Edition: Community
Git Commit Hash: 40b72e7a9a4fc9670d4c5d974dd503a3c6097471
Git Branch: heads/refs/tags/v7.3.0
UTC Build Time: 2023-08-08 10:08:14
GoVersion: go1.20.7
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.0011 sec)