mysql modify character set

MySQL is a commonly used relational database management system that supports multiple character sets, such as UTF-8, GBK, ISO-8859-1, etc. Different character sets have different effects when storing and processing data. Therefore, when using the MySQL database, you need to choose an appropriate character set based on the actual situation.

When we create databases and tables, we can specify the character set, but what if we need to modify the character set after creating the table? Next, we will introduce how to modify the character set of MySQL database and tables.

Modify the database character set

If you want to modify the character set of the database, there are two methods: one is to modify the default character set in the MySQL configuration file, and the other is to modify the existing database. These two methods are introduced below.

Method 1: Modify the MySQL configuration file

1. Open the MySQL configuration file my.cnf or my.ini.

In Windows systems, the my.ini file is generally located in the MySQL installation directory, such as C:\Program Files\MySQL\MySQL Server 5.7\my.ini; in Linux systems, my.cnf The file is generally located in /etc/mysql/my.cnf.

2. Find the character set configuration item under the [mysqld] node.

In the my.cnf or my.ini file, you can find the character set configuration item under the [mysqld] node, as shown below:

[mysqld]
character-set-server=utf8

3. Change the character set Modify the configuration item to the required character set.

Modify the value of character-set-server to the required character set, such as GBK, and then save the modification.

[mysqld]
character-set-server=gbk

4. Restart the MySQL service.

After modifying the configuration file, you need to restart the MySQL service for the new character set to take effect.

Method 2: Modify the existing database

1. Enter the MySQL client.

In command line mode, enter the following command and press Enter, then enter the MySQL administrator account and password:

mysql -u root -p

2. View the current database character set.

After entering the MySQL client, you can view the current database character set through the following command:

show variables like '%character%';

3. Modify the database character set.

Execute the following SQL statement in the MySQL client to modify the database character set, where database_name is the database name that needs to be modified, and charset_name is the required character set.

alter database database_name character set charset_name;

For example, if you want to change the character set of the database named my_database to GBK, you can execute the following SQL statement:

alter database my_database character set gbk;

4. Confirm that the modification is successful.

After executing the modification statement, you can run the following command again to view the current database character set and confirm whether the modification has taken effect:

show variables like '%character%';

Modify the table character set

When we When you need to modify the character set of the table, you can do it through the following steps:

1. Enter the MySQL client.

Same as when modifying the database character set, first enter the MySQL client.

2. View the character set of the current table.

After entering the MySQL client, you can view the character set of the current table through the following command, where table_name is the name of the table that needs to be viewed:

show create table table_name\G

This command The table structure definition can be output in the MySQL client. character set represents the character set of the table.

3. Modify the character set of the table.

Execute the following SQL statement in the MySQL client to modify the character set of the table to the required character set, where table_name is the name of the table that needs to be modified,column_name is the field name whose character set needs to be modified, charset_name is the required character set.

alter table table_name modify column_name charactor set charset_name;

For example, if we want to change the character set of the name field in the table named my_table to GBK, we can execute the following SQL statement:

alter table my_table modify name charactor set gbk;

After executing this SQL statement, the character set of the name field in the my_table table is modified to GBK.

4. Confirm the modification is successful.

After modifying the character set of the table, you can run the show create table table_name\G command in the second step again to confirm whether the modification takes effect.

In addition to the methods introduced above, there are other ways to modify the character set, such as modifying table structure files, using transcoding tools, etc. In general, modifying the character set through configuration files or SQL statements is a relatively simple and common method. You just need to choose the method that suits you.

In summary, the character set modification of MySQL database and table is relatively simple. As long as you choose the appropriate method, you can quickly modify the character set. However, it should be noted that modifying the character set will affect existing data and future data. Therefore, if data already exists in the database and table, be sure to back up the data before modifying the character set.

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