## 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
