## mysql自带test数据库表的默认属性：Collation latin1_swedish_ci 更新为utf8_general_ci，解决MYSQL数据库乱码
USE test;
CREATE TABLE SYS_LOG (
ID INT(20) NOT NULL ,
USERNAME VARCHAR(50) NULL ,
OPERATION VARCHAR(50) NULL ,
LOGTIME INT(11) NULL ,
METHOD VARCHAR(200) NULL ,
PARAMS VARCHAR(500) NULL ,
IP VARCHAR(64) NULL ,
CREATE_TIME DATE NULL
);

-- 查看所有字符集信息(从命令中查询)
SHOW CHARACTER SET;
-- 查看utf8字符集信息(从命令中查询)
SHOW CHARACTER SET LIKE 'utf8'; # utf8 UTF-8 Unicode utf8_general_ci

-- 查看所有排列字符集(从命令中查询)
SHOW COLLATION;
-- 查看utf8的所有排列字符集(从命令中查询)
SHOW COLLATION WHERE CHARSET = 'utf8'; # utf8_general_ci utf8

USE information_schema;
-- 查看所有字符集信息 (从数据库中查询)
SELECT * FROM CHARACTER_SETS;
-- 查看所有排列字符集信息 (从数据库中查询)
SELECT * FROM COLLATIONS;

USE test;
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

-- 直接查询information_schema中的数据
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test';
-- 在指定库中查询表的信息
USE test;
SHOW TABLE STATUS;
SHOW TABLE STATUS WHERE NAME LIKE 'sys_log';

##修改数据库的编码
ALTER DATABASE database_name CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
##修改表的编码(修改)
ALTER TABLE sys_log CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

##查看列属性
SHOW FULL COLUMNS FROM sys_log;
SHOW FULL COLUMNS FROM sys_log WHERE FIELD = 'logtime';
##修改列的编码
ALTER TABLE sys_log MODIFY USERNAME VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
ALTER TABLE sys_log MODIFY OPERATION VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
ALTER TABLE sys_log MODIFY METHOD VARCHAR(200) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
ALTER TABLE sys_log MODIFY PARAMS VARCHAR(500) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
ALTER TABLE sys_log MODIFY IP VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

##link: https://blog.csdn.net/ghosind/article/details/83692869