+5 votes
372 views
How to change the collation of all the tables in MySQL

in Help by (552k points) | 372 views

1 Answer

+3 votes
Best answer

It is necessary to change the collation of all the tables in a database from mysql, I have installed by default with latin_swedish and I want to change it to utf8_general_ci, usually the documentation that we are given to do it one at a time, even with the phpmyadmin, we have an option to do it completely to the whole database, but it does not work very well, at least it does not work for me, it did not give me an error, but it did not do anything, but with this script we can do it simple way, we throw this statement sql.

SELECT CONCAT(
'ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_general_ci;  ', 
'ALTER TABLE ',  table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  ')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'your_database_name'
AND
(C.CHARACTER_SET_NAME != 'utf8'
    OR
 C.COLLATION_NAME not like 'utf8%')

Once we return the set of sentences, we simply need to copy and launch it again, and all the collate of all the tables are changed to what we needed.


by (552k points)

Related questions

+3 votes
1 answer
asked Sep 2, 2022 in Help by backtothefuture (552k points) | 104 views
+5 votes
1 answer
asked May 27, 2020 in Help by backtothefuture (552k points) | 2.2k views
+3 votes
1 answer
asked Sep 2, 2022 in Help by backtothefuture (552k points) | 107 views
+3 votes
1 answer
Sponsored articles cost $40 per post. You can contact us via Feedback

Most popular questions within the last 30 days

10,659 questions
10,791 answers
510 comments
3 users