修改表字符集

修改表的字符集并使历史行生效

ALTER TABLE customer CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

 查看字段的字符集和排序字符集

select table_schema,COLLATION_NAME,count(COLLATION_NAME) num
from information_schema.`COLUMNS`
where TABLE_SCHEMA in (select schema_name from information_schema.SCHEMATA where schema_name in
      ('pay','md','perfdca','df','sdfa') )
      and COLLATION_NAME is not null
group by table_schema,COLLATION_NAME
order by table_schema,COLLATION_NAME;

select table_schema,cc.TABLE_NAME,cc.COLUMN_TYPE,cc.CHARACTER_SET_NAME,cc.COLLATION_NAME
from information_schema.`COLUMNS` cc
where TABLE_SCHEMA in (select schema_name from information_schema.SCHEMATA where schema_name in
('pay_auth','pay_trade') )
and COLLATION_NAME is not null;

 查看库、表、字段的字符集和排序字符集

mysql> select * from information_schema.SCHEMATA where schema_name in('pd','fee');

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)

mysql> show full columns from pay_trade.account_trans_info
+---------------+-----------------+--------------------+------+-----+---------+-------+---------------------------------+------------------+
| Field         | Type            | Collation          | Null | Key | Default | Extra | Privileges                      | Comment          |
+---------------+-----------------+--------------------+------+-----+---------+-------+---------------------------------+------------------+
| tid           | bigint unsigned | NULL               | NO   | PRI | NULL    |       | select,insert,update,references | 序号             |
| merchant_id   | varchar(32)     | utf8mb4_unicode_ci | NO   |     | NULL    |       | select,insert,update,references | 支付机构商户编码 |
| merchant_no   | varchar(32)     | utf8mb4_unicode_ci | YES  | MUL | NULL    |       | select,insert,update,references | 支付中心商户号   |
| service_tid   | bigint          | NULL               | YES  |     | NULL    |       | select,insert,update,references | 服务中心id       |
...
+---------------+-----------------+--------------------+------+-----+---------+-------+---------------------------------+------------------+