How does MySQL determine when to show explicit character set & collation values?

user_877676 0
Viewed 251

It's my understanding that field-level character set and collation are determined when a column is created. So I'm confused on why the following produces different output in SHOW CREATE TABLE. Since the explicit values of text_col2 are the same as the table's defaults, shouldn't those be "hidden" in the SHOW CREATE TABLE output, like they are for text_col1?

Additionally, I can't find anything in the INFORMATION_SCHEMA tables that shows how these two columns are defined differently. How/why is MySQL determining that?

> CREATE TABLE foo
(
    text_col1 varchar(64),
    text_col2 varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci

) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


> show create table foo;
CREATE TABLE `foo` (
  `text_col1` varchar(64) DEFAULT NULL,
  `text_col2` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


> select COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME from information_schema.COLUMNS WHERE TABLE_NAME = 'foo';

+-------------+--------------------+--------------------+
| COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     |
+-------------+--------------------+--------------------+
| text_col1   | utf8mb4            | utf8mb4_0900_ai_ci |
| text_col2   | utf8mb4            | utf8mb4_0900_ai_ci |
+-------------+--------------------+--------------------+

Why is MySQL showing the explicit values for text_col2 and not text_col1?

Here's another example. If I convert the table's default charset to utf8, then BOTH columns have the same definition in SHOW CREATE TABLE. But when I switch back to utf8mb4, they are different

> ALTER TABLE foo CHARACTER SET utf8;
> show create table foo;
CREATE TABLE `foo` (
  `text_col1` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `text_col2` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- Change back
> ALTER TABLE foo CHARACTER SET utf8mb4;
> show create table foo;
CREATE TABLE `foo` (
  `text_col1` varchar(64) DEFAULT NULL,
  `text_col2` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Where/How is MySQL determining that one is explicit vs implicit? And what impact does this have on functionality?

This is MySQL 8.0.23

mysql
1 Answers

The code suggests it has some way to keep track of when you assigned a character set to a column explicitly. This doesn't show up in the information_schema, but somehow it keeps track of it.

https://github.com/mysql/mysql-server/blob/8.0/sql/sql_show.cc#L1975-L1976

/*
  For string types dump charset name only if field charset is same as
  table charset or was explicitly assigned.
 */

Edit: that comment should read, "...if field charset is not the same as..."

https://github.com/mysql/mysql-server/blob/8.0/sql/sql_show.cc#L1983-L1987

/*
  For string types dump collation name only if
  collation is not primary for the given charset
  or was explicitly assigned.
*/
Bill_Karwin_208600
Related