0

I have in my DB (MySQL 5.6 in a shared hosting server) several collations and I want to set all of them to utf8mb4_unicode_ci collation and also change the connection charset to utf8mb4

How can I do it using phpMyAdmin for my DB without going table by table?

Thanks! Ram

Ram
  • 11
  • 6

1 Answers1

0

Sorry, you have to do it table by table. However you can write a single SQL to generate all the ALTER TABLE .. CONVERT TO .. statements, then copy&paste them.

Caution: Since you are in 5.6 (or 5.5), you may hit the dreaded "767" (or MyISAM's "1000") limit, primarily if you have VARCHAR(255). Here are 5 workarounds: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes (That link also has a sample of such a SELECT.)

If your tables are currently MyISAM, you really should change to InnoDB. Tack this onto the ALTERs: ENGINE=InnoDB.

For conversion to InnoDB, see http://mysql.rjweb.org/doc.php/myisam2innodb

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Hi, I did it in 2 steps: SELECT CONCAT('ALTER TABLE `', tbl.`TABLE_SCHEMA`, '`.`', tbl.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') FROM `information_schema`.`TABLES` tbl WHERE tbl.`TABLE_SCHEMA` = 'DB_NAME' I got a long list of that I copied to the SQL again - but I got this error: #1071 - Specified key was too long; max key length is 1000 bytes So as far as I understand from the link you added before I should Upgrade to 5.7.7... - right? – Ram May 13 '20 at 06:08
  • @Ram: 255*4 > 1000. Either _first_ change 255 to 250 where relevant (and safe). Or pick a suitable workaround in my list. If you do go to InnoDB, then 250 will still be a problem, so you really should go to **191** if possible. (I added to my Answer.) – Rick James May 13 '20 at 17:57