How to modify the character set in mysql?-Mysql Tutorial-php.cn

How to modify the character set in mysql?

藏色散人

Release： 2020-09-17 14:19:08

Original

33041 people have browsed it

Mysql method to modify the character set: first modify the "my.ini" configuration file; then modify the database character set through the "alter database database name character set utf8;" statement; and finally restart the mysql database service.

How to modify the character set in mysql?

1. Modify the my.ini configuration file (mysql configuration file)

character_set_server = utf8 #设置字符集

Copy after login

Restart the mysql database service

View the current Database character set

show VARIABLES like &#39;character%&#39;;

Copy after login

2. Modify the database character set

alter database 数据库名 character set utf8;

Copy after login

ps: After modifying the database character set, you need to restart the mysql database.

3. Modify the table character set

ALTER TABLE  表名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Copy after login

Generate all table modification character set statements:

SELECT TABLE_NAME,CONCAT(&#39;ALTER TABLE  &#39;,TABLE_NAME,&#39; DEFAULT CHARACTER SET &#39;,a.DEFAULT_CHARACTER_SET_NAME,&#39; COLLATE &#39;,a.DEFAULT_COLLATION_NAME,&#39;;&#39;) executeSQL FROM information_schema.SCHEMATA a,information_schema.TABLES bWHERE a.SCHEMA_NAME=b.TABLE_SCHEMAAND a.DEFAULT_COLLATION_NAME!=b.TABLE_COLLATIONAND b.TABLE_SCHEMA=&#39;数据库名&#39;

Copy after login

4. Modify the column character set

ALTER TABLE  表名 CHANGE  列名  列名  VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

Copy after login

Generate all columns Modify character set statement:

select b.table_name,b.column_name,b.character_set_name,b.collation_name
,CONCAT(&#39;ALTER TABLE &#39;,b.table_name,&#39; MODIFY &#39;,b.column_name,&#39; &#39;,b.DATA_TYPE,&#39;(&#39;,b.CHARACTER_MAXIMUM_LENGTH,&#39;) &#39;,CASE WHEN b.COLUMN_DEFAULT IS NULL THEN &#39;&#39;  ELSE CONCAT(&#39;DEFAULT \&#39;&#39;,b.COLUMN_DEFAULT,&#39;\&#39;&#39;) END,&#39; COMMENT \&#39;&#39;,b.COLUMN_COMMENT,&#39;\&#39;;&#39;) executeSQL from information_schema.TABLES a,information_schema.COLUMNS b where  b.character_set_name IS NOT NULL and a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAMEAND a.TABLE_COLLATION!=b.COLLATION_NAMEand a.TABLE_SCHEMA=&#39;数据库名&#39;

Copy after login

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