Differences

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

--- useful:mysql-mariadb [2022/11/27 15:38] – admin
+++ useful:mysql-mariadb [2024/01/29 11:59] – admin
@@ Line 71: / Line 71: @@
      [Service]
      ProtectHome=false
+It should be possible to achieve this without knowing exactly where the file is by using:
+     systemctl edit mariadb
+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