MySQL Sorting Rules: Complete Guide

1 year ago
Liam
1 minute

In MySQL, the COLLATE keyword can be used to set the sorting rules for fields. Sorting rules specify how text data should be sorted. There are two common sorting rules.

  1. UTF8_GENERAL_CI: A case-insensitive general sorting rule, suitable for most situations.
  2. UTF8_BIN: Case-sensitive binary sorting rule that precisely matches characters based on their case.

For example, you can specify the sorting rule of fields when creating a table.

CREATE TABLE example_table (
    id INT,
    name VARCHAR(50) COLLATE utf8_general_ci,
    email VARCHAR(50) COLLATE utf8_general_ci
);

If you need to specify a sorting order when querying, you can use the ORDER BY clause.

SELECT * FROM example_table ORDER BY name COLLATE utf8_bin;

This allows sorting fields in search results according to specified sorting rules.

bannerAds