How to alter multiple tables at once in mysql

Question

I want to change the character-set of all tables present in my DB. To change character set of one table I can execute it as:

"alter table mydb.mytable1 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_Ci;"

But i Have many tables in my 'my db' database. List of Tables we can read from information_schema table. But i am not sure how to form the Exact query so that with one query I can change character set of all the tables at once.

score 1 · Answer 1 · answered Jun 13 '17 at 17:20

1

-- https://stackoverflow.com/a/39465494/819417
SELECT concat('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db_name'

Then copy/paste all results and use a db tool or scripting language to run them one by one.

To fix future tables as you add them, run this as well:

ALTER DATABASE your_db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

answered Jun 13 '17 at 17:20

Cees Timmerman

17,623
11
91
124

Thanks +Cees Timmerman that helped out perfectly – Mark Ellul Mar 02 '23 at 00:50

Manwal · Answer 2 · 2014-09-22T10:28:23.443

0

Alter Database:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Note: Use "utf8_unicode_ci" instead of "utf8_general_ci"

So if you can't do this with Alter DB then you have to run this for every table individually

edited Sep 22 '14 at 10:28

answered Sep 22 '14 at 10:22

Manwal

23,450
12
63
93

I tried this option. But if I alter database it won't change the character set of columns of the table. – Pratik Sep 22 '14 at 10:26
@Pratik see update. It can't be done with single alter query you have to use them seperatly – Manwal Sep 22 '14 at 10:28
Or is there any script/procedure which can be written? – Pratik Sep 22 '14 at 10:36

How to alter multiple tables at once in mysql

2 Answers2

Linked