All geek questions in one place

The difference between database, table, column sorting

I understand that mappings are a set of rules for character set comparisons. MySQL has a mapping of tables and databases in addition to sorting columns. I was wondering what is the difference between sorting by these three (database, table and column).

Thanks a lot.

+6
source share
2 answers

MySQL character sets and mappings can be interpreted as a descending list of priority items. The highest is the lowest priority, and the lowest is the highest priority.

Priority order with lowest priority:

  • Server Sort
  • Connection Dependent Mapping
  • Database Sort
  • Table Mapping
  • Column Sort
  • Sorting queries (using CAST or CONVERT )

The server configuration is set by the server, which is installed either inside my.cnf , or when the server was created from the source code. By default, it will usually be latin1 or utf8 , depending on your platform.

Connection-specific consolidation is set by the client using a query of the type SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'; . Most clients do not set the sorting for specific connections, so the server will use its own default, as described above.

Sorting the database is specified when creating the database or manually by updating it later. If you do not specify one of them, it will use the next higher-level sort, which will be either binding to a specific server or server mapping.

Comparing tables is the same as sorting a database, unless it is left empty, it will use the default database, then connection-specific, and then, finally, server sorting.

The column mapping uses the default collation as the default, and if there is no collation, it will track the chain to find the collation to use, stopping on the server if all others have not been set.

Request sorting is specified in the query using CAST or CONVERT , but otherwise the next available sorting in the chain will be used. There is no way to set this if you are not using the function.

See also the manual page for Character Set Support .

+14
source

In short. When you set up the server. to UTF-8. All databases created without a sort definition inherit from the Server.

 column iherits from table table inherits from database database inherits from server

However, you can overwrite the default server at one of these points. Then everything inherits from him.

+1
source

Source: https://habr.com/ru/post/971189/

More articles:


All Articles