CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Warren Niles
Warren Niles

Reputation: 204

MySQL SELECT * fails upon Unicode

Background Information: I have a table of country_types. The charset is set to utf8mb4 and the colation is set to utf8mb4_0900_ai_ci.

When I run SELECT * FROM DB.country_types; I get the following:

Result of: SELECT * FROM DB.country_types in MySQL Workbench

However, as you can see entry number 29 is missing. When I filter to entry 29, it shows me entry 29, eventhough the SELECT * statement doesn't select entry 29.

Result of: Filter for entry 29

Entry 29 uses a special unicode character unlike the other rows. I was wondering how I could fix this problem in order to return entry 29 in the SELECT statement.

Upvotes: 3

Views: 176

Answers (1)

GMB
GMB

Reputation: 222632

When I run SELECT * FROM DB.country_types [...], as you can see entry number 29 is missing.

It is not missing, it is just not showing at the place where you expect it to be. It is not a matter of encoding. You have to keep in mind that, in any SQL database, records are unordered, unless an ORDER BY clause is specified. When no such clause is specified, the order of the records returned by the query is unpredictable.

If you want to consistently see entry 29 between 28 and 30, you need to add an ORDER BY clause to your query, like:

SELECT * FROM DB.country_types ORDER BY id;

Upvotes: 2

Related Questions