0

MySql version 5.6.39

i have one table that now needs to support chinese characters. the other tables in the database do not need utf8mb4 encoding.

what have i done thus far:
1) backed up database!
2) alter table <blah> default character set utf8mb4 collate utf8mb4_unicode_ci;
3) alter table <blah> convert to character set utf8mb4 collate utf8mb4_unicode_ci;
4) (for all columns except numeric) alter table <blah> change col1 col1 varchar(xx) character set utf8mb4 collate utf8mb4_unicode_ci;

what i have not done thus far:
1) alter database <blahDB> character set utf8mb4 collate utf8mb4_unicode_ci;

i've seen several questions/answers here...notibly: How to easily convert utf8 tables to utf8mb4 in MySQL 5.5 Is it necessary to alter the database when all i want is for one table within it to be utf8mb4?

Right now, when i insert a "special" (unicode) char (0x2015) in the middle of a string into the table, it is stored as '?'. Help!!

Improve this question
4
  • Do the changes to the columns show correctly when investigating the schema? SHOW FULL COLUMNS FROM <blah>;
    – KevH
    Nov 5, 2019 at 15:38
  • Yes! they all (except the "Id" numeric type column) show "utf8mb4_unicode_ci" collation.
    – David M
    Nov 5, 2019 at 15:46
  • How are you interacting with MySQL? If it's via an external client is the connection able to handle the characters? This link may help: [mathiasbynens.be/notes/mysql-utf8mb4]
    – KevH
    Nov 5, 2019 at 16:10
  • i have a java program that issues the relevant sql functions (INSERT, SELECT, etc). Lemme read that article...
    – David M
    Nov 5, 2019 at 16:20

1 Answer 1

Reset to default
0

Going by what you have to do within a PHP-Webserver environment to use utf8 / utf8mb4, you have up to 5 or more construction places. And you covered 2 of them

  • collation of table and column(s)
  • overwork existing string-data in said columns

leaves:

  • make sure your application / tool uses the correct collation as well
  • make sure you communicate with the database using the correct collation
  • set the frontend of your application / tool to handle said collation

As JAVA is well prepared I guess its the database connection ...

Improve this answer
1
  • in the Java code i had to convert the offending String to bytes("UTF8") prior to storing in the table. i had to reverse the conversion when i selected the record from the table. It works! thanks!
    – David M
    Nov 6, 2019 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.