2

The charset defines how the row data is saved into the file store.

Row 17: <CHARSET('utf8', 'hello'), 17, CHARSET('latin-1', 'sup')>

But it seems that collation only affects sorting of these rows.

So why is the collation specified when you are defining the column?

Instead I would expect that you would only specify the collation when defining things that matter with sorting rows, i.e. indexes (because they are stored sorted), queries (because they test equality and inequalities) and constraints (because they are effectively queries).

  • Also filtering. In some collations, ss=ß; in others, they are treated as unequal. – Rick James Dec 21 '19 at 2:02
  • Is that filtering different than a query? – William Entriken Dec 30 '19 at 16:27
  • @WilliamEntriken "Filtering" = comparison...typically in a WHERE clause, but can also be done in a JOIN. Basically, any place where operators such as =, <> / !=, <, >, <=, >=, LIKE, etc are used. – Solomon Rutzky Dec 30 '19 at 16:39
1

Unless MySQL / MariaDB does something that I'm unaware of (being that I typically focus on SQL Server): because encoding (i.e. "character set") is handled separately, column-level collation should simply be a default used for those operations that you mentioned (creating indexes, queries, etc), and comparisons, so that you don't need to specify the collation each and every time (strings cannot be either compared or sorted if the specific rules for doing so are not provided). That would not only greatly complicate queries, it would make it nearly impossible to provide consistency across queries as not having it declared at the column level (or at least at the DB level) would leave it to query developers to remember the intention of the data modeler, and that's certainly not a recipe for success ;-).

If not specified at the column level, collation needs to at least be specified at the database level or even the instance level (and I believe there are RDBMSs that do specify at either level, and maybe don't even allow for specifying at the column level).

Collation just needs to be specified somewhere, else you can't even rebuild an index (let alone create it) without providing the collation. And, allowing the granularity of specifying collation at the column level allows for maximum flexibility as different string columns within a table, or between tables in the same database, can easily have different needs / requirements.

And, just as a side note: for RDBMSs such as SQL Server that combine encoding along with collation in a singular "Collation" (and no way to separate the two), it is a necessity to specify at the column level as it indicates the encoding (at least for VARCHAR / CHAR / TEXT column; NVARCHAR / NCHAR / NTEXT are always UTF-16 LE).

|improve this answer|||||
  • So basically collation is part of the data model because the data designer cares if 'Straße' = 'Strasse'. – William Entriken Dec 30 '19 at 16:30
  • @WilliamEntriken I would say that yes, it is something that the data modeler/designer cares about because collation is an inherent part of the datatype. But please review the updates I just made to this answer. Thanks :) – Solomon Rutzky Dec 30 '19 at 17:31
1

(OP) But it seems that collation only affects sorting of these rows.

That's not true.

CREATE TABLE test1 (txt VARCHAR(4) COLLATE 'latin1_general_ci')
SELECT 'Test' txt UNION ALL
SELECT 'TEST'     UNION ALL
SELECT 'tEsT';
CREATE TABLE test2 (txt VARCHAR(4) COLLATE 'latin1_general_cs')
SELECT 'Test' txt UNION ALL
SELECT 'TEST'     UNION ALL
SELECT 'tEsT';
SELECT COUNT(DISTINCT txt)
FROM test1;
| COUNT(DISTINCT txt) |
| ------------------: |
|                   1 |
SELECT COUNT(DISTINCT txt)
FROM test2;
| COUNT(DISTINCT txt) |
| ------------------: |
|                   3 |

db<>fiddle here

The same while specify collation in a query:

SELECT COUNT(DISTINCT CONVERT(txt USING 'latin1_general_c?'))
FROM test;

(OP) why is the collation specified when you are defining the column?

Collation granularity is single value.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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