Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
The information_schema.TABLES table does not currently show a table's default character set.
For example, see here:
MariaDB [(none)]> SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA='db1' AND TABLE_NAME='tab'\G |
*************************** 1. row ***************************
|
TABLE_CATALOG: def
|
TABLE_SCHEMA: db1
|
TABLE_NAME: tab
|
TABLE_TYPE: BASE TABLE |
ENGINE: InnoDB
|
VERSION: 10
|
ROW_FORMAT: Dynamic |
TABLE_ROWS: 0
|
AVG_ROW_LENGTH: 0
|
DATA_LENGTH: 16384
|
MAX_DATA_LENGTH: 0
|
INDEX_LENGTH: 0
|
DATA_FREE: 0
|
AUTO_INCREMENT: NULL |
CREATE_TIME: 2020-04-13 17:09:37
|
UPDATE_TIME: NULL |
CHECK_TIME: NULL |
TABLE_COLLATION: utf8mb4_unicode_ci
|
CHECKSUM: NULL |
CREATE_OPTIONS:
|
TABLE_COMMENT:
|
MAX_INDEX_LENGTH: 0
|
TEMPORARY: N |
It does have a TABLE_COLLATION column, which can be used to infer the table's default character set.
However, the documentation would be simpler and clearer if the information_schema.TABLES table had *both* the default character set and the collation.
The information_schema.SCHEMATA table already contains both values for databases:
MariaDB [(none)]> SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='db1'\G |
*************************** 1. row ***************************
|
CATALOG_NAME: def
|
SCHEMA_NAME: db1
|
DEFAULT_CHARACTER_SET_NAME: utf8mb4
|
DEFAULT_COLLATION_NAME: utf8mb4_unicode_ci
|
SQL_PATH: NULL |
The table's default character set can currently be found from SHOW CREATE TABLE:
MariaDB [(none)]> SHOW CREATE TABLE db1.tab\G |
*************************** 1. row ***************************
|
Table: tab |
Create Table: CREATE TABLE `tab` ( |
`id` int(11) NOT NULL, |
`str` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |