SQL排序规则无效

Question

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?

Answer 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.