Rechercher dans le manuel MySQL
12.10 Cast Functions and Operators
Cast functions and operators enable conversion of values from one data type to another.
CONVERT()
with a
USING
clause provides a way to convert data
between different character sets:
In MySQL, transcoding names are the same as the corresponding character set names.
Examples:
You can also use CONVERT()
without
USING
or CAST()
to convert strings between different character sets:
Examples:
If you specify CHARACTER SET
as just shown,
the resulting character set and collation are
charset_name
charset_name
and the default collation
of charset_name
. If you omit
CHARACTER SET
, the resulting
character set and collation are defined by the
charset_name
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”).
A COLLATE
clause is not permitted within a
CONVERT()
or
CAST()
call, but you can apply it
to the function result. For example, this is legal:
But this is illegal:
Normally, you cannot compare a BLOB
value or other binary string in case-insensitive fashion because
binary strings use the binary
character set,
which has no collation with the concept of lettercase. To perform
a case-insensitive comparison, use the
CONVERT()
or
CAST()
function to convert the
value to a nonbinary string. Comparisons of the resulting string
use its collation. For example, if the conversion result character
set has a case-insensitive collation, a
LIKE
operation is not case-sensitive:
To use a different character set, substitute its name for
latin1
in the preceding statement. To specify a
particular collation for the converted string, use a
COLLATE
clause following the
CONVERT()
call:
CONVERT()
and
CAST()
can be used more generally
for comparing strings that are represented in different character
sets. For example, a comparison of these strings results in an
error because they have different character sets:
- ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
Converting one of the strings to a character set compatible with the other enables the comparison to occur without error:
- +---------------------------------+
- +---------------------------------+
- | 1 |
- +---------------------------------+
For string literals, another way to specify the character set is
to use a character set introducer (_latin1
and
_latin2
in the preceding example are instances
of introducers). Unlike conversion functions such as
CAST()
, or
CONVERT()
, which convert a string
from one character set to another, an introducer designates a
string literal as having a particular character set, with no
conversion involved. For more information, see
Section 10.3.8, “Character Set Introducers”.
Character set conversion is also useful preceding lettercase
conversion of binary strings.
LOWER()
and
UPPER()
are ineffective when
applied directly to binary strings because the concept of
lettercase does not apply. To perform lettercase conversion of a
binary string, first convert it to a nonbinary string:
- +-------------+------------------------------------+
- +-------------+------------------------------------+
- | New York | new york |
- +-------------+------------------------------------+
If you convert an indexed column using
BINARY
,
CAST()
, or
CONVERT()
, MySQL may not be able to
use the index efficiently.
The cast functions are useful for creating a column with a
specific type in a
CREATE TABLE ...
SELECT
statement:
- *************************** 1. row ***************************
- Table: new_table
The cast functions are useful for sorting
ENUM
columns in lexical order.
Normally, sorting of ENUM
columns
occurs using the internal numeric values. Casting the values to
CHAR
results in a lexical sort:
CAST()
also changes the result if
you use it as part of a more complex expression such as
CONCAT('Date: ',CAST(NOW() AS
DATE))
.
For temporal values, there is little need to use
CAST()
to extract data in different
formats. Instead, use a function such as
EXTRACT()
,
DATE_FORMAT()
, or
TIME_FORMAT()
. See
Section 12.7, “Date and Time Functions”.
To cast a string to a number, you normally need do nothing other than use the string value in numeric context:
- -> 2
That is also true for hexadecimal and bit literals, which are binary strings by default:
A string used in an arithmetic operation is converted to a floating-point number during expression evaluation.
A number used in string context is converted to a string:
For information about implicit conversion of numbers to strings, see Section 12.2, “Type Conversion in Expression Evaluation”.
MySQL supports arithmetic with both signed and unsigned 64-bit
values. For numeric operators (such as
+
or
-
) where one of the
operands is an unsigned integer, the result is unsigned by default
(see Section 12.6.1, “Arithmetic Operators”). To override this,
use the SIGNED
or UNSIGNED
cast operator to cast a value to a signed or unsigned 64-bit
integer, respectively.
- -> -1
- -> 18446744073709551615
- -> -1
If either operand is a floating-point value, the result is a
floating-point value and is not affected by the preceding rule.
(In this context, DECIMAL
column
values are regarded as floating-point values.)
The SQL mode affects the result of conversion operations (see Section 5.1.11, “Server SQL Modes”). Examples:
For conversion of a “zero” date string to a date,
CONVERT()
andCAST()
returnNULL
and produce a warning when theNO_ZERO_DATE
SQL mode is enabled.For integer subtraction, if the
NO_UNSIGNED_SUBTRACTION
SQL mode is enabled, the subtraction result is signed even if any operand is unsigned.
The following list describes the available cast functions and operators:
BINARY
expr
The
BINARY
operator converts the expression to a binary string. A common use forBINARY
is to force a character string comparison to be done byte by byte rather than character by character, in effect becoming case-sensitive. TheBINARY
operator also causes trailing spaces in comparisons to be significant.- -> 1
- -> 0
- -> 1
- -> 0
In a comparison,
BINARY
affects the entire operation; it can be given before either operand with the same result.For purposes of converting a string expression to a binary string, these constructs are equivalent:
If a value is a string literal, it can be designated as a binary string without performing any conversion by using the
_binary
character set introducer:For information about introducers, see Section 10.3.8, “Character Set Introducers”.
The
BINARY
operator in expressions differs in effect from theBINARY
attribute in character column definitions. A character column defined with theBINARY
attribute is assigned table default character set and the binary (_bin
) collation of that character set. Every nonbinary character set has a_bin
collation. For example, the binary collation for theutf8
character set isutf8_bin
, so if the table default character set isutf8
, these two column definitions are equivalent:The use of
CHARACTER SET binary
in the definition of aCHAR
,VARCHAR
, orTEXT
column causes the column to be treated as the corresponding binary string data type. For example, the following pairs of definitions are equivalent:The
CAST()
function takes an expression of any type and produces a result value of the specified type, similar toCONVERT()
. For more information, see the description ofCONVERT()
.In MySQL 8.0.17 and later,
InnoDB
allows the use of an additionalARRAY
keyword for creating a multi-valued index on aJSON
array as part ofCREATE INDEX
,CREATE TABLE
, andALTER TABLE
statements.ARRAY
is not supported except when used to create a multi-valued index in one of these statements, in which case it is required. The column being indexed must be a column of typeJSON
. (CONVERT()
does not support multi-valued index creation or theARRAY
keyword.) Thetype
following theAS
keyword may be any of the types supported byCAST()
, with the exception ofBINARY
, which is not supported for this purpose. Multi-Valued Indexes, provides syntax information and examples, as well as other relevant information.CAST()
is standard SQL syntax.CONVERT(
,expr
,type
)CONVERT(
expr
USINGtranscoding_name
)The
CONVERT()
function takes an expression of any type and produces a result value of the specified type.Discussion of
CONVERT(
syntax here also applies toexpr
,type
)CAST(
, which is equivalent.expr
AStype
)CONVERT(... USING ...)
is standard SQL syntax. The non-USING
form ofCONVERT()
is ODBC syntax.CONVERT()
withUSING
converts data between different character sets. In MySQL, transcoding names are the same as the corresponding character set names. For example, this statement converts the string'abc'
in the default character set to the corresponding string in theutf8
character set:CONVERT()
withoutUSING
andCAST()
take an expression and atype
value specifying the result type. Thesetype
values are permitted:BINARY[(
N
)]Produces a string with the
BINARY
data type. See Section 11.4.2, “The BINARY and VARBINARY Types” for a description of how this affects comparisons. If the optional lengthN
is given,BINARY(
causes the cast to use no more thanN
)N
bytes of the argument. Values shorter thanN
bytes are padded with0x00
bytes to a length ofN
.CHAR[(
N
)] [charset_info
]Produces a string with the
CHAR
data type. If the optional lengthN
is given,CHAR(
causes the cast to use no more thanN
)N
characters of the argument. No padding occurs for values shorter thanN
characters.With no
charset_info
clause,CHAR
produces a string with the default character set. To specify the character set explicitly, thesecharset_info
values are permitted:CHARACTER SET
: Produces a string with the given character set.charset_name
ASCII
: Shorthand forCHARACTER SET latin1
.UNICODE
: Shorthand forCHARACTER SET ucs2
.
In all cases, the string has the default collation for the character set.
DATE
Produces a
DATE
value.DATETIME
Produces a
DATETIME
value.DECIMAL[(
M
[,D
])]Produces a
DECIMAL
value. If the optionalM
andD
values are given, they specify the maximum number of digits (the precision) and the number of digits following the decimal point (the scale).DOUBLE
Produces a
DOUBLE
result. Added in MySQL 8.0.17.FLOAT[(
P
)]If the precision
P
is not specified, produces a result of typeFLOAT
. IfP
is provided and 0 <= <P
<= 24, the result is of typeFLOAT
. If 25 <=P
<= 53, the result is of typeREAL
. IfP
< 0 orP
> 53, an error is returned. Added in MySQL 8.0.17.JSON
Produces a
JSON
value. For details on the rules for conversion of values betweenJSON
and other types, see Comparison and Ordering of JSON Values.NCHAR[(
N
)]Like
CHAR
, but produces a string with the national character set. See Section 10.3.7, “The National Character Set”.Unlike
CHAR
,NCHAR
does not permit trailing character set information to be specified.REAL
Produces a result of type
REAL
. This is actuallyFLOAT
ifREAL_AS_FLOAT
SQL mode is enabled; otherwise the result is of typeDOUBLE
.SIGNED [INTEGER]
Produces a signed integer value.
TIME
Produces a
TIME
value.UNSIGNED [INTEGER]
Produces an unsigned integer value.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-cast-functions.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.