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

How to modify the character set in batches in mysql

coldplay.xixi

Release： 2023-01-04 09:37:30

Original

5073 people have browsed it

How to modify the character set in batches in mysql: First, you need to use statements to generate all actually executed statements; then based on the MySQL metadata table, get a set of directly executable SQL lists; finally, paste the statements directly and execute it.

How to modify the character set in batches in mysql

The operating environment of this tutorial: Windows 7 system, mysql version 8.0.22, Dell G3 computer.

Related free learning recommendations: mysql database(Video)

##mysql How to modify the character set in batches:

1. Modify the database encoding and character set

This step is relatively simple, just execute it directly:

ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;

Copy after login

2. Modifying the encoding and character set of the data table and fields in the table

requires two steps.

First, you need to use statements to generate all actually executed statements:

SELECT 
CONCAT("ALTER TABLE `", TABLE_NAME,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;") 
AS target_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="db_name"
AND TABLE_TYPE="BASE TABLE"

Copy after login

This statement will get a set of directly executable SQL lists based on the MySQL metadata table, as follows:

ALTER TABLE `table1` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `table2` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `table3` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `table4` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `table5` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `table6` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

Copy after login

Among them, table1 to table6 are all data tables in the database.

Then, paste the statement directly and execute it.

Note that

CONVERT TO is used here instead of DEFAULT because the latter will not modify the encoding and character set of the fields in the table.

In addition, for databases with many data tables, you can first export the execution results of the first step to the

.sql file, and then execute it through the SQL file.

Related free learning recommendations:
php programming (video)

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