什么是(默认的)字符集:
MySQL数据库 MySQL表 MySQL列
什么是(默认的)字符集:
MySQL数据库 MySQL表 MySQL列
当前回答
表:
SHOW TABLE STATUS将列出所有的表。
使用过滤:
SHOW TABLE STATUS where name like 'table_123';
其他回答
我是这么做的——
对于模式(或数据库-它们是同义词):
SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "schemaname";
表:
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema = "schemaname"
AND T.table_name = "tablename";
列:
SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "schemaname"
AND table_name = "tablename"
AND column_name = "columnname";
正如前面所写的,SHOW FULL COLUMNS应该是获取列信息的首选方法。 缺少的是在不直接到达元数据表的情况下获取字符集的方法:
SHOW FULL COLUMNS FROM my_table WHERE Field = 'my_field'
SHOW COLLATION WHERE Collation = 'collation_you_got'
数据库:
USE your_database_name;
show variables like "character_set_database";
-- or:
-- show variables like "collation_database";
参考本页。查阅MySQL手册
在创建新数据库时,将生成一些必要的表
在“information_schema”中此路径
“列”——>关于列
“表”——>表
例如,如果您需要查看表中的所有列名和类型
SELECT COLUMN_NAME,COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_schema_name' AND TABLE_NAME = 'your_table_name'
对于服务器上的所有数据库:
mysql> SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
输出:
+----------------------------+---------+--------------------+
| database | charset | collation |
+----------------------------+---------+--------------------+
| information_schema | utf8 | utf8_general_ci |
| my_database | latin1 | latin1_swedish_ci |
...
+----------------------------+---------+--------------------+
对于单个数据库:
mysql> USE my_database;
mysql> show variables like "character_set_database";
输出:
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| character_set_database | latin1 |
+----------------------------+---------+
获取表的排序规则:
mysql> USE my_database;
mysql> SHOW TABLE STATUS WHERE NAME LIKE 'my_tablename';
OR -将输出创建表的完整SQL语句:
Mysql >创建表my_tablename
获取列的排序规则:
mysql> SHOW FULL COLUMNS FROM my_tablename;
输出:
+---------+--------------+--------------------+ ....
| field | type | collation |
+---------+--------------+--------------------+ ....
| id | int(10) | (NULL) |
| key | varchar(255) | latin1_swedish_ci |
| value | varchar(255) | latin1_swedish_ci |
+---------+--------------+--------------------+ ....