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

Source: Internet
Author: User

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.

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.