0

I am trying to insert some utf8 encoded data into a mysql database. The special characters are correctly displayed in the browser, but not in my database. After manually changing the collation to utf8_unicode_ci and confirming that "this operation will attempt to convert your data to the new collation", the data gets displayed correctly. However if I create the table using

CREATE TABLE IF NOT EXISTS table_name (
  date date NOT NULL,
  searchengine VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  location VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  keyword VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  position INT NOT NULL,
  competition VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  url VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL    
)

and insert the data after creating the table, the data is still not shown correctly, even though the colletion is utf8_unicode_ci. Any ideas on how to fix this?

erlDan
  • 131
  • 4
  • 14
  • You have not provided enough information to answer -- are you getting question marks? Gibberish? Truncated text? What? In any case, see this for fixing your problem: http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James Sep 25 '17 at 20:35

1 Answers1

1

A collation is a set of rules that defines how to compare and sort character strings. Each collation in MySQL belongs to a single character set. Every character set has at least one collation, and most have two or more collations. A collation orders characters based on weights.

utf8mb4_unicode_ci is based on the Unicode standard for sorting and comparison, which sorts accurately in a very wide range of languages.

  • Manually setting the collation to utf8mb4_unicode_ci in phpmyadmin after inserting the data works fine. The data gets converted correctly. However setting the collation to utf8mb4_unicode_ci when creating the table like in the code shown above and inserting the data thereafter doesn't work. The data doesn't get converted. – erlDan Sep 25 '17 at 08:49
  • https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-conversion.html –  Sep 25 '17 at 08:56
  • I think you need to use utf8 instead of utf8mb4 . Please check the link in the above comment. –  Sep 25 '17 at 08:57
  • I already used utf8 in the initial query shown in the question details. The result is the same. – erlDan Sep 25 '17 at 09:31
  • 1
    @erlDan - `CHARACTER SET utf8mb4` is needed for Emoji and Chinese. For practically everything else, `CHARACTER SET utf8` will suffice. – Rick James Sep 25 '17 at 20:37