How to modify the mysql character set in batches

How to modify the mysql character set in batches? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

The method of batch modification of the mysql table character set is as follows: first, query all the table names of the database; then concatenate the table names into the statements that modify the table character set; finally, copy these statements to execute.

The method of batch modifying the character set of mysql table:

Change the table encoding (character set):

ALTER TABLE TABLE_NAME DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

If a database has a lot of tables to modify, it is a headache and time-consuming if there is no good way. There are many methods, and the following one is relatively simple and easy to operate.

1. A statement that queries all table names of a database:

SELECT TABLE_NAME from information_ schema.`TABLES` WHERE TABLE_SCHEMA = 'DATABASE_NAME'

2. Get all the table names. We can concatenate the table names into the above statement that modifies the table character set, and get the following statement:

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

What you get after execution is the statements that modify all the table character sets in the database, and then copy the statements for execution.

Let me take the demo database as an example:

Thank you for reading! After reading the above, do you have a general idea of how to modify the mysql character set in batches? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.