How to batch modify the character set of mysql table

How to batch modify the character set of mysql tables: first query all table names in the database; then splice the table names into the statements that modify the table character set; finally copy these statements and execute them.

How to batch modify the character set of mysql table:

Change table encoding (character set):

ALTER TABLE TABLE_NAME DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

If there are many tables in a database that need to be modified, it will be very troublesome and time-consuming if there is no good way. There are many methods, the following one is relatively simple and easy to operate.

1. Query the statement of all table names in a database:

SELECT TABLE_NAME from information_schema.`TABLES` WHERE TABLE_SCHEMA = 'DATABASE_NAME';

2. To get all the table names, we can splice the table names into the statement above to modify the table character set, The following statement is obtained:

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') from information_schema.`TABLES` WHERE TABLE_SCHEMA = 'DATABASE_NAME';

After execution, what you get is the statement that modifies the character set of all tables in the database, and then just copy these statements and execute them.

I will use the demo database as an example to operate:

##More related free learning recommendations: mysql tutorial(Video)

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