Here's my query:
SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') AS mySQL
FROM INFORMATION_SCHEMA.TABLES;
I want to change the charset and collation from each varchar and table from the whole database, but the result set from this is just huge. How can I iterate over this query and execute each result in the SQL Language?
If I were doing this I would save the output of this SELECT statement to a file with the filetype .sql.
Then I would edit the .sql file with a text editor to make sure it contains what I hoped for.
Then I would use a MySQL client program to run the SQL file.
Note well The SQL data definition language commands written by your query won't work. Your query writes a command for every table in the system. You probably want to do this for the tables in one database.
SELECT CONCAT('ALTER TABLE `',
TABLE_SCHEMA,
'``',
TABLE_NAME,
'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;')
AS mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA', 'MYSQL')
This query qualifies the table names with the data base name. It also chooses only the tables in the current database. Finally, it excludes the three system databases, because it's a really bad idea to try to alter tables in those databases.
