Rechercher dans le manuel MySQL
12.5.3 Character Set and Collation of Function Results
MySQL has many operators and functions that return a string. This section answers the question: What is the character set and collation of such a string?
For simple functions that take string input and return a string
result as output, the output's character set and collation are
the same as those of the principal input value. For example,
UPPER(
returns a string with the same character string and collation as
X
)X
. The same applies for
INSTR()
,
LCASE()
,
LOWER()
,
LTRIM()
,
MID()
,
REPEAT()
,
REPLACE()
,
REVERSE()
,
RIGHT()
,
RPAD()
,
RTRIM()
,
SOUNDEX()
,
SUBSTRING()
,
TRIM()
,
UCASE()
, and
UPPER()
.
The REPLACE()
function, unlike
all other functions, always ignores the collation of the
string input and performs a case-sensitive comparison.
If a string input or function result is a binary string, the
string has the binary
character set and
collation. This can be checked by using the
CHARSET()
and
COLLATION()
functions, both of
which return binary
for a binary string
argument:
- +---------------------+-----------------------+
- +---------------------+-----------------------+
- +---------------------+-----------------------+
For operations that combine multiple string inputs and return a single string output, the “aggregation rules” of standard SQL apply for determining the collation of the result:
If an explicit
COLLATE
occurs, useY
Y
.If explicit
COLLATE
andY
COLLATE
occur, raise an error.Z
Otherwise, if all collations are
Y
, useY
.Otherwise, the result has no collation.
For example, with CASE ... WHEN a THEN b WHEN b THEN c
COLLATE
, the
resulting collation is X
ENDX
. The same
applies for UNION
,
||
,
CONCAT()
,
ELT()
,
GREATEST()
,
IF()
, and
LEAST()
.
For operations that convert to character data, the character set
and collation of the strings that result from the operations are
defined by the
character_set_connection
and
collation_connection
system
variables that determine the default connection character set
and collation (see Section 10.4, “Connection Character Sets and Collations”). This
applies only to BIN_TO_UUID()
,
CAST()
,
CONV()
,
FORMAT()
,
HEX()
, and
SPACE()
.
An exception to the preceding priniciple occurs for expressions
for virtual generated columns. In such expressions, the table
character set is used for
BIN_TO_UUID()
,
CONV()
, or
HEX()
results, regardless of
connection character set.
If there is any question about the character set or collation of
the result returned by a string function, use the
CHARSET()
or
COLLATION()
function to find out:
- +----------------+-----------------+-------------------+
- +----------------+-----------------+-------------------+
- | test@localhost | utf8 | utf8_general_ci |
- +----------------+-----------------+-------------------+
- +--------------------------+----------------------------+
- +--------------------------+----------------------------+
- +--------------------------+----------------------------+
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-string-functions-charset.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.