Rechercher dans le manuel MySQL

10.3.8 Character Set Introducers

Inhaltsverzeichnis

A character string literal, hexadecimal literal, or bit-value literal may have an optional character set introducer and COLLATE clause, to designate it as a string that uses a particular character set and collation:

  1. [_charset_name] literal [COLLATE collation_name]

The _charset_name expression is formally called an introducer. It tells the parser, “the string that follows uses character set charset_name.” An introducer does not change the string to the introducer character set like CONVERT() would do. It does not change the string value, although padding may occur. The introducer is just a signal.

For character string literals, space between the introducer and the string is permitted but optional.

Examples:

  1. SELECT 'abc';
  2. SELECT _latin1'abc';
  3. SELECT _binary'abc';
  4. SELECT _utf8mb4'abc' COLLATE utf8mb4_danish_ci;
  5.  
  6. SELECT _latin1 X'4D7953514C';
  7. SELECT _utf8mb4 0x4D7953514C COLLATE utf8mb4_danish_ci;
  8.  
  9. SELECT _latin1 b'1000001';
  10. SELECT _utf8mb4 0b1000001 COLLATE utf8mb4_danish_ci;

Character set introducers and the COLLATE clause are implemented according to standard SQL specifications.

Character string literals can be designated as binary strings by using the _binary introducer. Hexadecimal literals and bit-value literals are binary strings by default, so _binary is permitted, but normally unnecessary. _binary may be useful to preserve a hexadecimal or bit literal as a binary string in contexts for which the literal is otherwise treated as a number. For example, bit operations permit numeric or binary string arguments in MySQL 8.0 and higher, but treat hexadecimal and bit literals as numbers by default. To explicitly specify binary string context for such literals, use a _binary introducer for at least one of the arguments:

  1. mysql> SET @v1 = X'000D' | X'0BC0';
  2. mysql> SET @v2 = _binary X'000D' | X'0BC0';
  3. mysql> SELECT HEX(@v1), HEX(@v2);
  4. +----------+----------+
  5. | HEX(@v1) | HEX(@v2) |
  6. +----------+----------+
  7. | BCD      | 0BCD     |
  8. +----------+----------+

The displayed result appears similar for both bit operations, but the result without _binary is a BIGINT value, whereas the result with _binary is a binary string. Due to the difference in result types, the displayed values differ: High-order 0 digits are not displayed for the numeric result.

MySQL determines the character set and collation of a character string literal, hexadecimal literal, or bit-value literal in the following manner:

  • If both _charset_name and COLLATE collation_name are specified, character set charset_name and collation collation_name are used. collation_name must be a permitted collation for charset_name.

  • If _charset_name is specified but COLLATE is not specified, character set charset_name and its default collation are used. To see the default collation for each character set, use the SHOW CHARACTER SET statement or query the INFORMATION_SCHEMA CHARACTER_SETS table.

  • If _charset_name is not specified but COLLATE collation_name is specified:

    • For a character string literal, the connection default character set given by the character_set_connection system variable and collation collation_name are used. collation_name must be a permitted collation for the connection default character set.

    • For a hexadecimal literal or bit-value literal, the only permitted collation is binary because these types of literals are binary strings by default.

  • Otherwise (neither _charset_name nor COLLATE collation_name is specified):

    • For a character string literal, the connection default character set and collation given by the character_set_connection and collation_connection system variables are used.

    • For a hexadecimal literal or bit-value literal, the character set and collation are binary.

Examples:

  • Nonbinary strings with latin1 character set and latin1_german1_ci collation:

    1. SELECT _latin1'Müller' COLLATE latin1_german1_ci;
    2. SELECT _latin1 X'0A0D' COLLATE latin1_german1_ci;
    3. SELECT _latin1 b'0110' COLLATE latin1_german1_ci;

  • Nonbinary strings with utf8mb4 character set and its default collation (that is, utf8mb4_0900_ai_ci):

    1. SELECT _utf8mb4'Müller';
    2. SELECT _utf8mb4 X'0A0D';
    3. SELECT _utf8mb4 b'0110';

  • Binary strings with binary character set and its default collation (that is, binary):

    1. SELECT _binary'Müller';
    2. SELECT X'0A0D';
    3. SELECT b'0110';

    The hexadecimal literal and bit-value literal need no introducer because they are binary strings by default.

  • A nonbinary string with the connection default character set and utf8mb4_general_ci collation (fails if the connection character set is not utf8mb4):

    1. SELECT 'Müller' COLLATE utf8mb4_general_ci;

    This construction (COLLATE only) does not work for hexadecimal literals or bit literals because their character set is binary no matter the connection character set, and binary is not compatible with the utf8mb4_general_ci collation. The only permitted COLLATE clause in the absence of an introducer is COLLATE binary.

  • A string with the connection default character set and collation:

    1. SELECT 'Müller';

For character set literals, an introducer indicates the character set for the following string, but does not change how the parser performs escape processing within the string. Escapes are always interpreted by the parser according to the character set given by character_set_connection. For additional discussion and examples, see Section 10.3.6, “Character String Literal Character Set and Collation”.


Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-charset-introducer.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut