How to query the character set encoding of a table in mysql

Two methods for mysql query table character set encoding: 1. Use the "show table status" statement to view the character set encoding of the specified table in the specified database. The syntax is "show table status from database name like table name;" . 2. Use the "show columns" statement with the full keyword to view the character set encoding of all columns of the specified table in the current database. The syntax is "show full columns from table name;".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Two methods for mysql query table character set encoding

1. Use the show table status statement View the character set encoding of the specified table

SHOW TABLE STATUS command can obtain information about each data table in the specified database, including the character set encoding.

show table status from 数据库名;

But if you only want to obtain the information of the specified table, you can use like to limit it:

show table status from 库名 like 表名;

Example: View the character set encoding of the test_info table in the class_7 database

show table status from class_7 like 'test_info';

mysql> show table status from class_7 like 'test_info';
+-----------+--------+---------+------------+------+----------------+-------------------------+-------------+------------+-----------------+----------+-
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_leate_time         | Update_time | Check_time | Collation       | Checksum | 
+-----------+--------+---------+------------+------+----------------+-------------------------+-------------+------------+-----------------+----------+-
| test_info | InnoDB |      10 | Compact    |   10 |           1638 |       17-12-05 19:01:55 | NULL        | NULL       | utf8_general_ci |     NULL | 
+-----------+--------+---------+------------+------+----------------+-------------------------+-------------+------------+-----------------+----------+-
1 row in set (0.00 sec)

2. Use the show columns statement with the full keyword to view the character set encoding of all columns in the specified table in the current database

In In mysql, the SHOW COLUMNS command can display the column information of the table, and to get more information about the columns, please add the FULL keyword to SHOW COLUMNS In the command:

show full columns from 表名;

This statement can output the character set encoding of all columns in the specified table

Example: View the character set encoding of all columns in the test_info table

show full columns from test_info;

mysql> show full columns from test_info;
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type     | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(3)   | NULL            | NO   | PRI | NULL    |       | select,insert,update,references |         |
| name  | char(12) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| dorm  | char(10) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| addr  | char(12) | utf8_general_ci | YES  |     | 未知    |       | select,insert,update,references |         |
| score | int(3)   | NULL            | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
5 rows in set (0.00 sec)

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to query the character set encoding of a table in mysql. For more information, please follow other related articles on the PHP Chinese website!