Movatterモバイル変換

MySQL 9.1 Reference Manual

14.10 Cast Functions and Operators

Table 14.15 Cast Functions and Operators

Name	Description	Deprecated
`BINARY`	Cast a string to a binary string	Yes
`CAST()`	Cast a value as a certain type
`CONVERT()`	Cast a value as a certain type

Cast functions and operators enable conversion of values from one data type to another.

Cast Function and Operator Descriptions

BINARYexpr
TheBINARY operator converts the expression to a binary string (a string that has thebinary character set andbinary collation). A common use forBINARY is to force a character string comparison to be done byte by byte using numeric byte values rather than character by character. TheBINARY operator also causes trailing spaces in comparisons to be significant. For information about the differences between thebinary collation of thebinary character set and the_bin collations of nonbinary character sets, seeSection 12.8.5, “The binary Collation Compared to _bin Collations”.
TheBINARY operator is deprecated; you should expect its removal in a future version of MySQL. UseCAST(... AS BINARY) instead.
```
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;        -> OKmysql> SELECT 'a' = 'A';        -> 1mysql> SELECT BINARY 'a' = 'A';        -> 0mysql> SELECT 'a' = 'a ';        -> 1mysql> SELECT BINARY 'a' = 'a ';        -> 0
```
In a comparison,BINARY affects the entire operation; it can be given before either operand with the same result.
To convert a string expression to a binary string, these constructs are equivalent:
```
CONVERT(expr USING BINARY)CAST(expr AS BINARY)BINARYexpr
```
If a value is a string literal, it can be designated as a binary string without converting it by using the_binary character set introducer:
```
mysql> SELECT 'a' = 'A';        -> 1mysql> SELECT _binary 'a' = 'A';        -> 0
```
For information about introducers, seeSection 12.3.8, “Character Set Introducers”.
TheBINARY operator in expressions differs in effect from theBINARY attribute in character column definitions. For a character column defined with theBINARY attribute, MySQL assigns the table default character set and the binary (_bin) collation of that character set. Every nonbinary character set has a_bin collation. For example, if the table default character set isutf8mb4, these two column definitions are equivalent:
```
CHAR(10) BINARYCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
```
The use ofCHARACTER 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:
```
CHAR(10) CHARACTER SET binaryBINARY(10)VARCHAR(10) CHARACTER SET binaryVARBINARY(10)TEXT CHARACTER SET binaryBLOB
```
IfBINARY is invoked from within themysql client, binary strings display using hexadecimal notation, depending on the value of the--binary-as-hex. For more information about that option, seeSection 6.5.1, “mysql — The MySQL Command-Line Client”.
CAST(expr AStype [ARRAY])
CAST(timestamp_value AT TIME ZONEtimezone_specifier AS DATETIME[(precision)])
timezone_specifier: [INTERVAL] '+00:00' | 'UTC'
WithCAST(expr AStype syntax, theCAST() function takes an expression of any type and produces a result value of the specified type. This operation may also be expressed asCONVERT(expr,type), which is equivalent. Ifexpr isNULL,CAST() returnsNULL.
Thesetype values are permitted:
- BINARY[(N)]
  Produces a string with theVARBINARY data type, except that when the expressionexpr is empty (zero length), the result type isBINARY(0). If the optional lengthN is given,BINARY(N) causes the cast to use no more thanN bytes of the argument. Values shorter thanN bytes are padded with0x00 bytes to a length ofN. If the optional lengthN is not given, MySQL calculates the maximum length from the expression. If the supplied or calculated length is greater than an internal threshold, the result type isBLOB. If the length is still too long, the result type isLONGBLOB.
  For a description of how casting toBINARY affects comparisons, seeSection 13.3.3, “The BINARY and VARBINARY Types”.
- CHAR[(N)] [charset_info]
  Produces a string with theVARCHAR data type, unless the expressionexpr is empty (zero length), in which case the result type isCHAR(0). If the optional lengthN is given,CHAR(N) causes the cast to use no more thanN characters of the argument. No padding occurs for values shorter thanN characters. If the optional lengthN is not given, MySQL calculates the maximum length from the expression. If the supplied or calculated length is greater than an internal threshold, the result type isTEXT. If the length is still too long, the result type isLONGTEXT.
  With nocharset_info clause,CHAR produces a string with the default character set. To specify the character set explicitly, thesecharset_info values are permitted:
  - CHARACTER SETcharset_name: Produces a string with the given character set.
  - ASCII: Shorthand forCHARACTER SET latin1.
  - UNICODE: Shorthand forCHARACTER SET ucs2.
  In all cases, the string has the character set default collation.
- DATE
  Produces aDATE value.
- DATETIME[(M)]
  Produces aDATETIME value. If the optionalM value is given, it specifies the fractional seconds precision.
- DECIMAL[(M[,D])]
  Produces aDECIMAL 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). IfD is omitted, 0 is assumed. IfM is omitted, 10 is assumed.
- DOUBLE
  Produces aDOUBLE result.
- FLOAT[(p)]
  If the precisionp 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 typeDOUBLE. Ifp < 0 orp > 53, an error is returned.
- JSON
  Produces aJSON value. For details on the rules for conversion of values betweenJSON and other types, seeComparison and Ordering of JSON Values.
- NCHAR[(N)]
  LikeCHAR, but produces a string with the national character set. SeeSection 12.3.7, “The National Character Set”.
  UnlikeCHAR,NCHAR does not permit trailing character set information to be specified.
- REAL
  Produces a result of typeREAL. This is actuallyFLOAT if theREAL_AS_FLOAT SQL mode is enabled; otherwise the result is of typeDOUBLE.
- SIGNED [INTEGER]
  Produces a signedBIGINT value.
- spatial_type
  CAST() andCONVERT() support casting geometry values from one spatial type to another, for certain combinations of spatial types. For details, seeCast Operations on Spatial Types.
- TIME[(M)]
  Produces aTIME value. If the optionalM value is given, it specifies the fractional seconds precision.
- UNSIGNED [INTEGER]
  Produces an unsignedBIGINT value.
- YEAR
  Produces aYEAR value. These rules govern conversion toYEAR as follows:
  - For a four-digit number in the range 1901-2155 inclusive, or for a string which can be interpreted as a four-digit number in this range, return the correspondingYEAR value.
  - For a number consisting of one or two digits, or for a string which can be interpreted as such a number, return aYEAR value as follows:
    If the number is in the range 1-69 inclusive, add 2000 and return the sum.
    If the number is in the range 70-99 inclusive, add 1900 and return the sum.
  - For a string which evaluates to 0, return 2000.
  - For the number 0, return 0.
  - For aDATE,DATETIME, orTIMESTAMP value, return theYEAR portion of the value. For aTIME value, return the current year.
    If you do not specify the type of aTIME argument, you may get a different result from what you expect, as shown here:
    mysql> SELECT CAST("11:35:00" AS YEAR), CAST(TIME "11:35:00" AS YEAR);+--------------------------+-------------------------------+| CAST("11:35:00" AS YEAR) | CAST(TIME "11:35:00" AS YEAR) |+--------------------------+-------------------------------+| 2011 | 2021 |+--------------------------+-------------------------------+
  - If the argument is of typeDECIMAL,DOUBLE,DECIMAL, orREAL, round the value to the nearest integer, then attempt to cast the value toYEAR using the rules for integer values, as shown here:
    mysql> SELECT CAST(1944.35 AS YEAR), CAST(1944.50 AS YEAR);+-----------------------+-----------------------+| CAST(1944.35 AS YEAR) | CAST(1944.50 AS YEAR) |+-----------------------+-----------------------+| 1944 | 1945 |+-----------------------+-----------------------+mysql> SELECT CAST(66.35 AS YEAR), CAST(66.50 AS YEAR);+---------------------+---------------------+| CAST(66.35 AS YEAR) | CAST(66.50 AS YEAR) |+---------------------+---------------------+| 2066 | 2067 |+---------------------+---------------------+
  - An argument of typeGEOMETRY cannot be converted toYEAR.
  - For a value that cannot be successfully converted toYEAR, returnNULL.
  A string value containing non-numeric characters which must be truncated prior to conversion raises a warning, as shown here:
```
mysql> SELECT CAST("1979aaa" AS YEAR);+-------------------------+| CAST("1979aaa" AS YEAR) |+-------------------------+|                    1979 |+-------------------------+1 row in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS;+---------+------+-------------------------------------------+| Level   | Code | Message                                   |+---------+------+-------------------------------------------+| Warning | 1292 | Truncated incorrect YEAR value: '1979aaa' |+---------+------+-------------------------------------------+
```
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. WithARRAY, thetype following theAS keyword may specify any of the types supported byCAST(), with the exceptions ofBINARY,JSON, andYEAR. For syntax information and examples, as well as other relevant information, seeMulti-Valued Indexes.
Note
CONVERT(), unlikeCAST(), doesnot support multi-valued index creation or theARRAY keyword.
CAST() supports retrieval of aTIMESTAMP value as being in UTC, using theAT TIMEZONE operator. The only supported time zone is UTC; this can be specified as either of'+00:00' or'UTC'. The only return type supported by this syntax isDATETIME, with an optional precision specifier in the range of 0 to 6, inclusive.
TIMESTAMP values that use timezone offsets are also supported.
```
mysql> SELECT @@system_time_zone;+--------------------+| @@system_time_zone |+--------------------+| EDT                |+--------------------+1 row in set (0.00 sec)mysql> CREATE TABLE tz (c TIMESTAMP);Query OK, 0 rows affected (0.41 sec)mysql> INSERT INTO tz VALUES    ->     ROW(CURRENT_TIMESTAMP),    ->     ROW('2020-07-28 14:50:15+1:00');Query OK, 1 row affected (0.08 sec)mysql> TABLE tz;+---------------------+| c                   |+---------------------+| 2020-07-28 09:22:41 || 2020-07-28 09:50:15 |+---------------------+2 rows in set (0.00 sec)mysql> SELECT CAST(c AT TIME ZONE '+00:00' AS DATETIME) AS u FROM tz;+---------------------+| u                   |+---------------------+| 2020-07-28 13:22:41 || 2020-07-28 13:50:15 |+---------------------+2 rows in set (0.00 sec)mysql> SELECT CAST(c AT TIME ZONE 'UTC' AS DATETIME(2)) AS u FROM tz;+------------------------+| u                      |+------------------------+| 2020-07-28 13:22:41.00 || 2020-07-28 13:50:15.00 |+------------------------+2 rows in set (0.00 sec)
```
If you use'UTC' as the time zone specifier with this form ofCAST(), and the server raises an error such asUnknown or incorrect time zone: 'UTC', you may need to install the MySQL time zone tables (seePopulating the Time Zone Tables).
AT TIME ZONE does not support theARRAY keyword, and is not supported by theCONVERT() function.
CONVERT(expr USINGtranscoding_name)
CONVERT(expr,type)
CONVERT(expr USINGtranscoding_name) is standard SQL syntax. The non-USING form ofCONVERT() is ODBC syntax. Regardless of the syntax used, the function returnsNULL ifexpr isNULL.
CONVERT(expr USINGtranscoding_name) 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 theutf8mb4 character set:
```
SELECT CONVERT('abc' USING utf8mb4);
```
CONVERT(expr,type) syntax (withoutUSING) takes an expression and atype value specifying a result type, and produces a result value of the specified type. This operation may also be expressed asCAST(expr AStype), which is equivalent. For more information, see the description ofCAST().

Character Set Conversions

CONVERT() with aUSING clause converts data between character sets:

CONVERT(expr USINGtranscoding_name)

In MySQL, transcoding names are the same as the corresponding character set names.

Examples:

SELECT CONVERT('test' USING utf8mb4);SELECT CONVERT(_latin1'Müller' USING utf8mb4);INSERT INTO utf8mb4_table (utf8mb4_column)    SELECT CONVERT(latin1_column USING utf8mb4) FROM latin1_table;

To convert strings between character sets, you can also useCONVERT(expr,type) syntax (withoutUSING), orCAST(expr AStype), which is equivalent:

CONVERT(string, CHAR[(N)] CHARACTER SETcharset_name)CAST(string AS CHAR[(N)] CHARACTER SETcharset_name)

Examples:

SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4);SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4);

If you specifyCHARACTER SETcharset_name as just shown, the character set and collation of the result arecharset_name and the default collation ofcharset_name. If you omitCHARACTER SETcharset_name, the character set and collation of the result are defined by thecharacter_set_connection andcollation_connection system variables that determine the default connection character set and collation (seeSection 12.4, “Connection Character Sets and Collations”).

ACOLLATE clause is not permitted within aCONVERT() orCAST() call, but you can apply it to the function result. For example, these are legal:

SELECT CONVERT('test' USING utf8mb4) COLLATE utf8mb4_bin;SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;

But these are illegal:

SELECT CONVERT('test' USING utf8mb4 COLLATE utf8mb4_bin);SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);

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 asCAST(), orCONVERT(), 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, seeSection 12.3.8, “Character Set Introducers”.

Character Set Conversions for String Comparisons

Normally, you cannot compare aBLOB value or other binary string in case-insensitive fashion because binary strings use thebinary character set, which has no collation with the concept of lettercase. To perform a case-insensitive comparison, first use theCONVERT() orCAST() function to convert the value to a nonbinary string. Comparisons of the resulting string use its collation. For example, if the conversion result collation is not case-sensitive, aLIKE operation is not case-sensitive. That is true for the following operation because the defaultutf8mb4 collation (utf8mb4_0900_ai_ci) is not case-sensitive:

SELECT 'A' LIKE CONVERT(blob_col USING utf8mb4)  FROMtbl_name;

To specify a particular collation for the converted string, use aCOLLATE clause following theCONVERT() call:

SELECT 'A' LIKE CONVERT(blob_col USING utf8mb4) COLLATE utf8mb4_unicode_ci  FROMtbl_name;

To use a different character set, substitute its name forutf8mb4 in the preceding statements (and similarly to use a different collation).

CONVERT() andCAST() can be used more generally for comparing strings represented in different character sets. For example, a comparison of these strings results in an error because they have different character sets:

mysql> SET @s1 = _latin1 'abc', @s2 = _latin2 'abc';mysql> SELECT @s1 = @s2;ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT)and (latin2_general_ci,IMPLICIT) for operation '='

Converting one of the strings to a character set compatible with the other enables the comparison to occur without error:

mysql> SELECT @s1 = CONVERT(@s2 USING latin1);+---------------------------------+| @s1 = CONVERT(@s2 USING latin1) |+---------------------------------+|                               1 |+---------------------------------+

Character set conversion is also useful preceding lettercase conversion of binary strings.LOWER() andUPPER() 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 using a character set appropriate for the data stored in the string:

mysql> SET @str = BINARY 'New York';mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING utf8mb4));+-------------+------------------------------------+| LOWER(@str) | LOWER(CONVERT(@str USING utf8mb4)) |+-------------+------------------------------------+| New York    | new york                           |+-------------+------------------------------------+

Be aware that if you applyBINARY,CAST(), orCONVERT() to an indexed column, MySQL may not be able to use the index efficiently.

Cast Operations on Spatial Types

CAST() andCONVERT() support casting geometry values from one spatial type to another, for certain combinations of spatial types. The following list shows the permitted type combinations, where“MySQL extension” designates casts implemented in MySQL beyond those defined in theSQL/MM standard:

FromPoint to:
- MultiPoint
- GeometryCollection
FromLineString to:
- Polygon (MySQL extension)
- MultiPoint (MySQL extension)
- MultiLineString
- GeometryCollection
FromPolygon to:
- LineString (MySQL extension)
- MultiLineString (MySQL extension)
- MultiPolygon
- GeometryCollection
FromMultiPoint to:
- Point
- LineString (MySQL extension)
- GeometryCollection
FromMultiLineString to:
- LineString
- Polygon (MySQL extension)
- MultiPolygon (MySQL extension)
- GeometryCollection
FromMultiPolygon to:
- Polygon
- MultiLineString (MySQL extension)
- GeometryCollection
FromGeometryCollection to:
- Point
- LineString
- Polygon
- MultiPoint
- MultiLineString
- MultiPolygon

In spatial casts,GeometryCollection andGeomCollection are synonyms for the same result type.

Some conditions apply to all spatial type casts, and some conditions apply only when the cast result is to have a particular spatial type. For information about terms such as“well-formed geometry,” seeSection 13.4.4, “Geometry Well-Formedness and Validity”.

General Conditions for Spatial Casts

These conditions apply to all spatial casts regardless of the result type:

The result of a cast is in the same SRS as that of the expression to cast.
Casting between spatial types does not change coordinate values or order.
If the expression to cast isNULL, the function result isNULL.
Casting to spatial types using theJSON_VALUE() function with aRETURNING clause specifying a spatial type is not permitted.
Casting to anARRAY of spatial types is not permitted.
If the spatial type combination is permitted but the expression to cast is not a syntactically well-formed geometry, anER_GIS_INVALID_DATA error occurs.
If the spatial type combination is permitted but the expression to cast is a syntactically well-formed geometry in an undefined spatial reference system (SRS), anER_SRS_NOT_FOUND error occurs.
If the expression to cast has a geographic SRS but has a longitude or latitude that is out of range, an error occurs:
- If a longitude value is not in the range (−180, 180], anER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE error occurs.
- If a latitude value is not in the range [−90, 90], anER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE error occurs.
Ranges shown are in degrees. If an SRS uses another unit, the range uses the corresponding values in its unit. The exact range limits deviate slightly due to floating-point arithmetic.

Conditions for Casts to Point

When the cast result type isPoint, these conditions apply:

If the expression to cast is a well-formed geometry of typePoint, the function result is thatPoint.
If the expression to cast is a well-formed geometry of typeMultiPoint containing a singlePoint, the function result is thatPoint. If the expression contains more than onePoint, anER_INVALID_CAST_TO_GEOMETRY error occurs.
If the expression to cast is a well-formed geometry of typeGeometryCollection containing only a singlePoint, the function result is thatPoint. If the expression is empty, contains more than onePoint, or contains other geometry types, anER_INVALID_CAST_TO_GEOMETRY error occurs.
If the expression to cast is a well-formed geometry of type other thanPoint,MultiPoint,GeometryCollection, anER_INVALID_CAST_TO_GEOMETRY error occurs.

Conditions for Casts to LineString

When the cast result type isLineString, these conditions apply:

If the expression to cast is a well-formed geometry of typeLineString, the function result is thatLineString.
If the expression to cast is a well-formed geometry of typePolygon that has no inner rings, the function result is aLineString containing the points of the outer ring in the same order. If the expression has inner rings, anER_INVALID_CAST_TO_GEOMETRY error occurs.
If the expression to cast is a well-formed geometry of typeMultiPoint containing at least two points, the function result is aLineString containing the points of theMultiPoint in the order they appear in the expression. If the expression contains only onePoint, anER_INVALID_CAST_TO_GEOMETRY error occurs.
If the expression to cast is a well-formed geometry of typeMultiLineString containing a singleLineString, the function result is thatLineString. If the expression contains more than oneLineString, anER_INVALID_CAST_TO_GEOMETRY error occurs.
If the expression to cast is a well-formed geometry of typeGeometryCollection, containing only a singleLineString, the function result is thatLineString. If the expression is empty, contains more than oneLineString, or contains other geometry types, anER_INVALID_CAST_TO_GEOMETRY error occurs.
If the expression to cast is a well-formed geometry of type other thanLineString,Polygon,MultiPoint,MultiLineString, orGeometryCollection, anER_INVALID_CAST_TO_GEOMETRY error occurs.

Conditions for Casts to Polygon

When the cast result type isPolygon, these conditions apply:

If the expression to cast is a well-formed geometry of typeLineString that is a ring (that is, the start and end points are the same), the function result is aPolygon with an outer ring consisting of the points of theLineString in the same order. If the expression is not a ring, anER_INVALID_CAST_TO_GEOMETRY error occurs. If the ring is not in the correct order (the exterior ring must be counter-clockwise), anER_INVALID_CAST_POLYGON_RING_DIRECTION error occurs.
If the expression to cast is a well-formed geometry of typePolygon, the function result is thatPolygon.
If the expression to cast is a well-formed geometry of typeMultiLineString where all elements are rings, the function result is aPolygon with the firstLineString as outer ring and any additionalLineString values as inner rings. If any element of the expression is not a ring, anER_INVALID_CAST_TO_GEOMETRY error occurs. If any ring is not in the correct order (the exterior ring must be counter-clockwise, interior rings must be clockwise), anER_INVALID_CAST_POLYGON_RING_DIRECTION error occurs.
If the expression to cast is a well-formed geometry of typeMultiPolygon containing a singlePolygon, the function result is thatPolygon. If the expression contains more than onePolygon, anER_INVALID_CAST_TO_GEOMETRY error occurs.
If the expression to cast is a well-formed geometry of typeGeometryCollection containing only a singlePolygon, the function result is thatPolygon. If the expression is empty, contains more than onePolygon, or contains other geometry types, anER_INVALID_CAST_TO_GEOMETRY error occurs.
If the expression to cast is a well-formed geometry of type other thanLineString,Polygon,MultiLineString,MultiPolygon, orGeometryCollection, anER_INVALID_CAST_TO_GEOMETRY error occurs.

Conditions for Casts to MultiPoint

When the cast result type isMultiPoint, these conditions apply:

If the expression to cast is a well-formed geometry of typePoint, the function result is aMultiPoint containing thatPoint as its sole element.
If the expression to cast is a well-formed geometry of typeLineString, the function result is aMultiPoint containing the points of theLineString in the same order.
If the expression to cast is a well-formed geometry of typeMultiPoint, the function result is thatMultiPoint.
If the expression to cast is a well-formed geometry of typeGeometryCollection containing only points, the function result is aMultiPoint containing those points. If theGeometryCollection is empty or contains other geometry types, anER_INVALID_CAST_TO_GEOMETRY error occurs.
If the expression to cast is a well-formed geometry of type other thanPoint,LineString,MultiPoint, orGeometryCollection, anER_INVALID_CAST_TO_GEOMETRY error occurs.

Conditions for Casts to MultiLineString

When the cast result type isMultiLineString, these conditions apply:

If the expression to cast is a well-formed geometry of typeLineString, the function result is aMultiLineString containing thatLineString as its sole element.
If the expression to cast is a well-formed geometry of typePolygon, the function result is aMultiLineString containing the outer ring of thePolygon as its first element and any inner rings as additional elements in the order they appear in the expression.
If the expression to cast is a well-formed geometry of typeMultiLineString, the function result is thatMultiLineString.
If the expression to cast is a well-formed geometry of typeMultiPolygon containing only polygons without inner rings, the function result is aMultiLineString containing the polygon rings in the order they appear in the expression. If the expression contains any polygons with inner rings, anER_WRONG_PARAMETERS_TO_STORED_FCT error occurs.
If the expression to cast is a well-formed geometry of typeGeometryCollection containing only linestrings, the function result is aMultiLineString containing those linestrings. If the expression is empty or contains other geometry types, anER_INVALID_CAST_TO_GEOMETRY error occurs.
If the expression to cast is a well-formed geometry of type other thanLineString,Polygon,MultiLineString,MultiPolygon, orGeometryCollection, anER_INVALID_CAST_TO_GEOMETRY error occurs.

Conditions for Casts to MultiPolygon

When the cast result type isMultiPolygon, these conditions apply:

If the expression to cast is a well-formed geometry of typePolygon, the function result is aMultiPolygon containing thePolygon as its sole element.
If the expression to cast is a well-formed geometry of typeMultiLineString where all elements are rings, the function result is aMultiPolygon containing aPolygon with only an outer ring for each element of the expression. If any element is not a ring, anER_INVALID_CAST_TO_GEOMETRY error occurs. If any ring is not in the correct order (exterior ring must be counter-clockwise), anER_INVALID_CAST_POLYGON_RING_DIRECTION error occurs.
If the expression to cast is a well-formed geometry of typeMultiPolygon, the function result is thatMultiPolygon.
If the expression to cast is a well-formed geometry of typeGeometryCollection containing only polygons, the function result is aMultiPolygon containing those polygons. If the expression is empty or contains other geometry types, anER_INVALID_CAST_TO_GEOMETRY error occurs.
If the expression to cast is a well-formed geometry of type other thanPolygon,MultiLineString,MultiPolygon, orGeometryCollection, anER_INVALID_CAST_TO_GEOMETRY error occurs.

Conditions for Casts to GeometryCollection

When the cast result type isGeometryCollection, these conditions apply:

GeometryCollection andGeomCollection are synonyms for the same result type.
If the expression to cast is a well-formed geometry of typePoint, the function result is aGeometryCollection containing thatPoint as its sole element.
If the expression to cast is a well-formed geometry of typeLineString, the function result is aGeometryCollection containing thatLineString as its sole element.
If the expression to cast is a well-formed geometry of typePolygon, the function result is aGeometryCollection containing thatPolygon as its sole element.
If the expression to cast is a well-formed geometry of typeMultiPoint, the function result is aGeometryCollection containing the points in the order they appear in the expression.
If the expression to cast is a well-formed geometry of typeMultiLineString, the function result is aGeometryCollection containing the linestrings in the order they appear in the expression.
If the expression to cast is a well-formed geometry of typeMultiPolygon, the function result is aGeometryCollection containing the elements of theMultiPolygon in the order they appear in the expression.
If the expression to cast is a well-formed geometry of typeGeometryCollection, the function result is thatGeometryCollection.

Other Uses for Cast Operations

The cast functions are useful for creating a column with a specific type in aCREATE TABLE ... SELECT statement:

mysql> CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE) AS c1;mysql> SHOW CREATE TABLE new_table\G*************************** 1. row ***************************       Table: new_tableCreate Table: CREATE TABLE `new_table` (  `c1` date DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The cast functions are useful for sortingENUM columns in lexical order. Normally, sorting ofENUM columns occurs using the internal numeric values. Casting the values toCHAR results in a lexical sort:

SELECTenum_col FROMtbl_name   ORDER BY CAST(enum_col AS CHAR);

CAST() also changes the result if you use it as part of a more complex expression such asCONCAT('Date: ',CAST(NOW() AS DATE)).

For temporal values, there is little need to useCAST() to extract data in different formats. Instead, use a function such asEXTRACT(),DATE_FORMAT(), orTIME_FORMAT(). SeeSection 14.7, “Date and Time Functions”.

To cast a string to a number, it normally suffices to use the string value in numeric context:

mysql> SELECT 1+'1';       -> 2

That is also true for hexadecimal and bit literals, which are binary strings by default:

mysql> SELECT X'41', X'41'+0;        -> 'A', 65mysql> SELECT b'1100001', b'1100001'+0;        -> 'a', 97

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:

mysql> SELECT CONCAT('hello you ',2);        -> 'hello you 2'

For information about implicit conversion of numbers to strings, seeSection 14.3, “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 (seeSection 14.6.1, “Arithmetic Operators”). To override this, use theSIGNED orUNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

mysql> SELECT 1 - 2;        -> -1mysql> SELECT CAST(1 - 2 AS UNSIGNED);        -> 18446744073709551615mysql> SELECT CAST(CAST(1 - 2 AS UNSIGNED) AS SIGNED);        -> -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.)

mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;        -> -1.0

The SQL mode affects the result of conversion operations (seeSection 7.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 theNO_UNSIGNED_SUBTRACTION SQL mode is enabled, the subtraction result is signed even if any operand is unsigned.

PREV HOME UP NEXT