MySQL basics 06 data type (6) type conversion, mysql Data Type

MySQL basics 06 data type (6) type conversion, mysql Data Type
1.Cast () Usage

In MySQL, cast (value as target type) syntax can be used for conversion of most types.

 

Cast must be followed by left parentheses:

Mysql> select cast (123 as char );

+ ------------------- +

| Cast (123 as char) |

+ ------------------- +

| 1, 123 |

+ ------------------- +

1 row in set (0.00 sec)

 

An error is reported when there is a space between cast and left parentheses:

Mysql> select cast (123 as char );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Char) 'at line 1

Mysql>

 

2. Conversion of numeric and string types

Use cast () to convert a value to a string type:

Mysql> select cast (123.456 as char );

+ ----------------------- +

| Cast (123.456 as char) |

+ ----------------------- +

| 1, 123.456 |

+ ----------------------- +

1 row in set (0.00 sec)

When converting to a string, the parameter can only use char, but not varchar. When varchar is used, an error is returned:

Mysql> select cast (123 as varchar (10 ));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar (10 )) 'At line 1

 

 

Mysql> select cast (123 as char (10 ));

+ ----------------------- +

| Cast (123 as char (10) |

+ ----------------------- +

| 1, 123 |

+ ----------------------- +

1 row in set (0.00 sec)

 

Mysql> select cast (123 as char (2 ));

+ ---------------------- +

| Cast (123 as char (2) |

+ ---------------------- +

| 12 |

+ ---------------------- +

1 row in set, 1 warning (0.00 sec)

 

Mysql> show warnings;

+ --------- + ------ + -------------------------------------------- +

| Level | Code | Message |

+ --------- + ------ + -------------------------------------------- +

| Warning | 1292 | Truncated incorrect CHAR (2) value: '20140901' |

+ --------- + ------ + -------------------------------------------- +

1 row in set (0.00 sec)

You can use char (N) to specify the width. characters exceeding the width are automatically truncated.

 

 

Use cast () to convert a string to a numeric value.

Mysql> select cast ('100' as decimal (123) + cast ('100' as decimal (456 ));

+ ------------------------------------------------------------- +

| Cast ('20180101' as decimal (123) + cast ('20180101' as decimal (456) |

+ ------------------------------------------------------------- +

| 1, 579 |

+ ------------------------------------------------------------- +

1 row in set (0.00 sec)

When converting to a value, the parameter can only be decimal, not int.

 

Mysql> select cast ('20180101' as decimal );

+ ------------------------- +

| Cast ('20180101' as decimal) |

+ ------------------------- +

| 1, 123 |

+ ------------------------- +

1 row in set (0.00 sec)

 

 

Mysql> select cast ('123' as decimal (123 ));

+ ----------------------------- +

| Cast ('200' as decimal (123) |

+ ----------------------------- +

| 1, 123 |

+ ----------------------------- +

1 row in set (0.00 sec)

 

Mysql> select cast ('123' as decimal (123 ));

+ ----------------------------- +

| Cast ('200' as decimal (123) |

+ ----------------------------- +

| 99 |

+ ----------------------------- +

1 row in set, 1 warning (0.00 sec)

 

Mysql> show warnings;

+ --------- + ------ + ---------------------------------------------------------------------- +

| Level | Code | Message |

+ --------- + ------ + ---------------------------------------------------------------------- +

| Warning | 1264 | Out of range value for column 'Cast ('000000' as decimal (123) 'at row 1 |

+ --------- + ------ + ---------------------------------------------------------------------- +

1 row in set (0.00 sec)

 

When the range is exceeded, it is automatically converted to the nearest value that meets the range requirement.

 

If a type other than decimal is used as the target type, an error is returned:

Mysql> select cast ('000000' as float (10 ));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'float (10 )) 'At line 1

Mysql> select cast ('000000' as int );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int) 'at line 1

Mysql> select cast ('200' as numeric (123 ));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'numeric )) 'At line 1

Mysql>

 

 

3. Conversion between date type and Other Types

 

You can also use cast () to convert date-related types to string types.

Mysql> select cast ('2014-09-01 'as date );

+ ---------------------------- +

| Cast ('2014-09-01 'as date) |

+ ---------------------------- +

| 2017-09-01 |

+ ---------------------------- +

1 row in set (0.00 sec)

 

Mysql> select cast ('2017-09-01 'as datetime );

+ -------------------------------- +

| Cast ('2017-09-01 'as datetime) |

+ -------------------------------- +

| 00:00:00 |

+ -------------------------------- +

1 row in set (0.01 sec)

 

Mysql> select cast (now () as char );

+ --------------------- +

| Cast (now () as char) |

+ --------------------- +

| 17:38:24 |

+ --------------------- +

1 row in set (0.00 sec)

 

You can also use the date_format () function to obtain the formatted string.

Mysql> select date_format (now (), '% Y-% m-% d % H: % I: % s ');

+ ---------------------------------------- +

| Date_format (now (), '% Y-% m-% d % H: % I: % s') |

+ ---------------------------------------- +

| 17:41:22 |

+ ---------------------------------------- +

1 row in set (0.00 sec)

 

 

The date type can be implicitly converted to the string type in some functions.

Mysql> select concat (now (), 'abc ');

+ ------------------------ +

| Concat (now (), 'abc') |

+ ------------------------ +

| 2017-09-01 17: 38: 45abc |

+ ------------------------ +

1 row in set (0.00 sec)

 

 

The string type can also be implicitly converted to the date type in some date-related functions.

Mysql> select year ('2014-09-01 ');

+ -------------------- +

| Year ('2014-09-01 ') |

+ -------------------- +

| 1, 2017 |

+ -------------------- +

1 row in set (0.00 sec)

 

 

4. Conversion Between the JSON type and Other Types

Use CAST for type conversion. If the result is of the JSON type, use JSON_TYPE () to view the specific type.

 

Mysql> select json_type (cast ('1' as json ));

+ ------------------------------ +

| Json_type (cast ('1' as json) |

+ ------------------------------ +

| INTEGER |

+ ------------------------------ +

1 row in set (0.00 sec)

 

Mysql> select json_type (cast ("1" as json ));

+ ------------------------------ +

| Json_type (cast ("1" as json) |

+ ------------------------------ +

| INTEGER |

+ ------------------------------ +

1 row in set (0.00 sec)

 

Mysql> select json_type (cast ("abc" as json ));

ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.

Mysql> select json_type (cast ('"abc"' as json ));

+ ---------------------------------- +

| Json_type (cast ('"abc"' as json) |

+ ---------------------------------- +

| STRING |

+ ---------------------------------- +

1 row in set (0.00 sec)

 

 

Mysql> select json_type (cast ('true' as json ));

+ --------------------------------- +

| Json_type (cast ('true' as json) |

+ --------------------------------- +

| BOOLEAN |

+ --------------------------------- +

1 row in set (0.00 sec)

 

Mysql> select json_type (cast (true as json ));

+ ------------------------------- +

| Json_type (cast (true as json) |

+ ------------------------------- +

| BOOLEAN |

+ ------------------------------- +

1 row in set (0.00 sec)

 

 

Mysql> select json_type (cast ('{"a": "123", "B": "345"}' as json ));

+ -------------------------------------------------- +

| Json_type (cast ('{"a": "123", "B": "345"}' as json) |

+ -------------------------------------------------- +

| OBJECT |

+ -------------------------------------------------- +

1 row in set (0.00 sec)

 

 

If you only want to obtain the specific JSON type, you can directly enclose it in single quotes without using cast conversion.

Mysql> select json_type ('true ');

+ ------------------- +

| Json_type ('true') |

+ ------------------- +

| BOOLEAN |

+ ------------------- +

1 row in set (0.00 sec)

 

Mysql> select json_type ('{"a": "123", "B": "345 "}');

+ ------------------------------------ +

| Json_type ('{"a": "123", "B": "345"}') |

+ ------------------------------------ +

| OBJECT |

+ ------------------------------------ +

1 row in set (0.00 sec)

 

 

 

However, if you do not use single quotes, an error is returned:

Mysql> select json_type (true );

ERROR 3146 (22032): Invalid data type for JSON data in argument 1 to function json_type; a JSON

String or JSON type is required.