user3299633 user3299633 - 24 days ago 7
MySQL Question

mysql won't let me change collation type

I'm trying to convert my table collation type from unicode to general, but mysql isn't allowing it. No errors are returned, but when I view the table, I can see that no changes are being made.

mysql> show variables like '%coll%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> show create table abc;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc_test | CREATE TABLE `abc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`somecolumn` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> alter table abc convert to charset utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table abc;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc_test | CREATE TABLE `abc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`somecolumn` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Answer

Just use alter like this ALTER TABLE table_name COLLATE='utf8_general_ci';

Your table already has UTF8 charset. So, In my opinion you don't need to set it again. I can see that you are trying to convert it to charset utf8 again with your alter query.