How to modify the character set in batches in mysql

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.

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;

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"

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;

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!