How to modify collation in mysql

Method: 1. Set the "my.cnf" file and add "character_set_server=modified content" and "collation_server=modified content" under mysqld; 2. Use "systemctl restart mysqld" to restart. Can.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to modify collation in mysql

[Problem Error]
When inserting data into the database, an error occurs when a varchar type field is inserted into Chinese data.
Original error text:
ERROR 1366 (HY000): Incorrect string value: '\xE8\xA5\xBF\xE5\xAE\x89' for column 'address'

[Cause analysis 】
Check the collation attribute of the field through the show full columns from user_bean; statement and find that the value of the collation attribute of the field is latin1_swedish_ci, indicating that the field defaults to English. The Chinese language has not been set, so when entering Chinese, mysql will report an error as "The value of the string is incorrect."

[Solution]

1. Edit the mysql configuration file /etc/my.cnf, vi /etc/my .cnf

Add two lines below [mysqld] to add

character_set_server=utf8
collation_server=utf8_general_ci

2. Restart the Mysql servicesystemctl restart mysqld

Insert into the database Contains Chinese data, success!

Note: The above steps can solve the problem of the collation attribute of fields when creating tables in the future, but the collation attribute values ​​of previously created table fields will not change.
What should I do if I want to change a table that has been created before?
Method 1: To modify the original table, you can use similar statements
alter table user_bean change address address varchar(255) character set utf8 collate utf8_general_ci not null;
Method 2: Delete the original table and recreate it.

[Command summary]

show full columns from 表名

vi /etc/my.cnf
character_set_server=utf8
collation_server=utf8_general_ci

systemctl restart mysqld

alter table 表名 change 要修改的字段 字段名 数据类型  character set utf8 collate utf8_general_ci 约束条件;

Recommended learning: mysql video tutorial

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