Documentation by Black Sheep Research

User Tools

Site Tools

Trace:
useful:mysql-mariadb

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Last revisionBoth sides next revision
useful:mysql-mariadb [2022/11/27 15:40] – adminuseful:mysql-mariadb [2024/01/29 11:59] – admin
Line 77: Line 77:
            
 but when I tried, it seemed not to work. but when I tried, it seemed not to work.
 +
 +===== Change collation for all tables in a database =====
 +
 +Remember to set the correct name for the database in this code and make sure output file does not exist:
 +
 +     SET @MY_SCHEMA = "myremos_j41";
 +     SELECT DISTINCT
 +      CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as queries
 +     FROM INFORMATION_SCHEMA.TABLES
 +     WHERE TABLE_SCHEMA=@MY_SCHEMA
 +       AND TABLE_TYPE="BASE TABLE"
 +     INTO OUTFILE '/tmp/convert.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
 +     
 +Then run this command (changing database name as appropriate):
 +
 +     mysql myremos_j41 < /tmp/convert.csv     
 +
useful/mysql-mariadb.txt · Last modified: 2024/03/15 14:36 by admin