MariaDB database COLLATE change query

Question

I have 3 queries to change the COLLATION. They should work but they does not. Looks like only [!varchar] columns by 2nd query are affected. Can anybody tell what is the problem there? I need to convert from utf8_general_ci to utf8_bin. I get results like:

ALTER TABLE AO_21F425_MESSAGE_MAPPING_AO MODIFY MESSAGE_ID varchar CHARACTER SET UTF8 COLLATE utf8_bin NOT NULL;
ALTER TABLE AO_21F425_MESSAGE_MAPPING_AO MODIFY USER_HASH varchar CHARACTER SET UTF8 COLLATE utf8_bin NOT NULL;
ALTER TABLE AO_38321B_CUSTOM_CONTENT_LINK MODIFY LINK_URL varchar CHARACTER SET UTF8 COLLATE utf8_bin;

and so on... but no changes in DB.

1) Change columns with type VARCHAR

SELECT CONCAT('ALTER TABLE `', table_name,
        '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, 
        ') CHARACTER SET UTF8 COLLATE utf8_bin',
        (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'my_database_name'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);

2) Change columns with other than VARCHAR type

SELECT CONCAT('ALTER TABLE `', table_name,
    '` MODIFY `', column_name, '` ', DATA_TYPE,
    ' CHARACTER SET UTF8 COLLATE utf8_bin',
    (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'my_database_name'
AND DATA_TYPE != 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);

3) Change table level

SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_bin;')
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 = 'my_database_name'
AND
(
    C.CHARACTER_SET_NAME != 'utf8'
    OR
    C.COLLATION_NAME != 'utf8_bin'
);

Rick James · Answer 1 · 2017-11-18T00:45:15.943

0

Beware; the ALTER may not be what you want!

Are you coming from charset latin1 and the data is correctly encoded as latin1 now? Then you need ALTER TABLE ... CONVERT TO ...

Are you coming from latin1, but the bytes were utf8? Then you need two alters.

What is your exact situation?

After determining what you really need, please provide the resulting ALTERs for us to critique.

These links may help:

Diagnosis, Best practice, Common problems: Trouble with UTF-8 characters; what I see is not what I stored
Fixes: http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases

more

In

ALTER  TABLE AO_21F425_MESSAGE_MAPPING_AO MODIFY MESSAGE_ID
      varchar CHARACTER SET UTF8 COLLATE utf8_bin NOT NULL;

you must specify the size of the varchar.

edited Nov 18 '17 at 00:45

answered Nov 15 '17 at 18:52

Rick James

135,179
13
127
222

Tnx. I edited the question. From UTF8_GENERAL_CI to UTF8_BIN conversion required in my case. – Dzintars Nov 15 '17 at 19:50
So, you want to change the `COLLATION` of columns that are _already_ `CHARSET utf8`. Let's see the generated `ALTERs`. – Rick James Nov 16 '17 at 02:53
Not sure i got You, but this is some of query results: ALTER TABLE `AO_21F425_MESSAGE_MAPPING_AO` MODIFY `MESSAGE_ID` varchar CHARACTER SET UTF8 COLLATE utf8_bin NOT NULL; ALTER TABLE `AO_21F425_MESSAGE_MAPPING_AO` MODIFY `USER_HASH` varchar CHARACTER SET UTF8 COLLATE utf8_bin NOT NULL; ALTER TABLE `AO_38321B_CUSTOM_CONTENT_LINK` MODIFY `LINK_URL` varchar CHARACTER SET UTF8 COLLATE utf8_bin; and so on. – Dzintars Nov 17 '17 at 13:18
@Dzintars - I added some more. – Rick James Nov 18 '17 at 00:45
Tnx. I am not a SQL guy, i can do just simple SQL queries. Can i ask you to copy-paste/edit the query to show me how to set varchar size automatically? What i need to achieve - i have database with data in it. There is 200+ tables so i don't want to do it manually. I want to have query which will make that conversion at once. I need to have utf8_bin COLLATE at all levels - db, tables and columns. – Dzintars Nov 19 '17 at 14:51
@Dzintars - Try using `COLUMN_TYPE` instead of `DATA_TYPE`; that should give you `varchar(123)` instead of simply `varchar`. – Rick James Nov 19 '17 at 19:02

score 0 · Accepted Answer · answered Nov 22 '17 at 16:22

I found solution for my problem. It does its thing very well. Just paste in console.

DB="your_database_name"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_bin;'; mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;' ) | mysql "$DB"

MariaDB database COLLATE change query

2 Answers2