Home  >  Article  >  Database  >  How to perform type conversion in mysql

How to perform type conversion in mysql

青灯夜游
青灯夜游Original
2022-01-05 14:59:0021441browse

Conversion method: 1. Use the " " operator, the syntax is "SELECT 1 'String';"; 2. Use the CAST() function to convert any type to a specified type, the syntax is "CAST(expr AS type)"; 3. Use the DATE_FORMAT() function to convert the date into a string according to the given pattern.

How to perform type conversion in mysql

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

1. The concept of implicit type conversion and explicit type conversion

Implicit type conversion:

To perform operations or comparisons between two values, the data types must first be consistent. Implicit type conversion occurs when two data types are found to be inconsistent. For example, convert a string into a number, or vice versa:

SELECT 1+'1'; – 字符串1转成数字
SELECT concat(2,'test'); – 数字2转成字符串

Explicit type conversion:

Use functions to convert data types

2.Cast function

##CAST(expr AS type)

Convert any type of expression expr into a value of the specified type type. type can be any of the following types:

  • BINARY[(N)]: binary string, the length after conversion is less than N bytes (if the length is less than N, 0x00 will be added at the end)

  • CHAR[(N)]: String, the length after conversion is less than N characters

  • DATE: Date

  • DATETIME: Date and time

  • DECIMAL[(M[,N])]: Floating point number, M is the total number of digits (including the integer part and the decimal part) , N is the number of digits after the decimal point

  • ##SIGNED [INTEGER]: Signed integer
  • TIME: Time
  • UNSIGNED [INTEGER]: Unsigned integer
Convert string to number

When converting to decimal, scan characters from the beginning String up to the first non-numeric character. Round the truncated digit

1) By default, M and N are not limited, converted to an integer, and rounded to the first decimal place:

SELECT cast(‘28.82abc’ AS DECIMAL);

How to perform type conversion in mysql

2) Limit M but not limit N, convert it to an integer (M should be as large as possible):

SELECT cast(‘28.82abc’ AS DECIMAL(4));

How to perform type conversion in mysqlIf M When the length is less than the actual number of digits, it will be converted to the maximum value of the set number of digits. In the following conversion, M is 1, that is, the two-digit number 29 cannot be displayed, and the largest one-digit number, 9

SELECT cast(‘28.82abc’ AS DECIMAL(1));

How to perform type conversion in mysql, is limited to M and N, which must be First satisfy N decimal places after the decimal point. In the following example, 1 decimal place is satisfied first, and then 1 integer is taken:

SELECT cast(‘28.82abc’ AS DECIMAL(2,1));

How to perform type conversion in mysql 2 decimal places are satisfied first, and the integer part is not taken:

SELECT cast(‘28.82abc’ AS DECIMAL(2,2));

SELECT cast(‘28.82abc’ AS UNSIGNED);
How to perform type conversion in mysql

How to perform type conversion in mysql

SELECT cast(’-28abc’ AS SIGNED);

How to perform type conversion in mysql

##String to date

SELECT cast(‘2007-12-25’ AS DATETIME);

SELECT cast(‘2007-12-25’ AS DATE);

How to perform type conversion in mysql

SELECT cast(‘20:20:20’ AS TIME);

How to perform type conversion in mysql

The date format must be 'YYYY-MM-DD'How to perform type conversion in mysql

SELECT cast(‘25-12-2007’ AS DATETIME);
SELECT cast(‘2007-may-25’ AS DATETIME);

How to perform type conversion in mysql
How to perform type conversion in mysql

Number to string

SELECT cast(123 AS CHAR);
 SELECT cast(123 AS BINARY);

Date to string

SELECT cast(now() AS CHAR);

How to perform type conversion in mysql

String to binary string

二进制字符串:二进制字符串被视为一个连续的字节序列,与字符集无关。非二进制字符串(即我们通常所说的字符串)被视为一个连续排列的字符序列,与字符集有关。所谓与字符集无关,是指与MySQL自己的字符集无关,而是按照操作系统的字符集把字符串转换成字节进行存储

两种字符串的比较方式:二进制字符串的比较方式是一个字节一个字节进行的,比较的依据是两个字节的二进制值。因为同一个字母的大小写的数值编码是不一样的,因此它是区分大小写的。另外,由于它和字符集无关,因此也就没有大写和小写字母一说

非二进制字符串的比较方式是一个字符一个字符进行的,比较的依据是两个字符在字符集中的先后顺序。根据使用的校对不同,可以进行区分大小写的比较和不区分大小写的比较

使用CAST(str AS BINARY)将字符串转换成二进制字符串时,通常使用它的快捷方式写法: BINARY str

查看当前字符集和校对规则设置

SHOW VARIABLES LIKE ‘collation_%’;

How to perform type conversion in mysql

在比较表达式中,binary影响后面所有的字符串,并且不会忽略字符串的尾部空格

SELECT ‘a’ = ‘A’ ,BINARY ‘a’ = ‘A’;
 SELECT ‘a’ = 'a ',BINARY ‘a’ = 'a ';

How to perform type conversion in mysql

How to perform type conversion in mysql

Cast(col_name as date_type)

将指定的列或者表达式转换为指定的数据类型

使用场景:当两个值进行比较,但是数据源的表中列的数据不一致,这个时候可以使用cast函数进行转化

3.Convert函数

CONVERT(expr, type), CONVERT(expr USING sharset_name)

convert函数的作用和cast函数几乎相同,但是它可以把字符串从一种字符集转换成另一种字符集。下例将字符串’abc’从默认的字符集转换成utf8字符集

SELECT convert(‘abc’ USING utf8);

How to perform type conversion in mysql

SELECT convert(‘你好’ USING gbk);

How to perform type conversion in mysql

如果目标字符集不能表示该字符,则返回乱码

SELECT convert(‘你好’ USING latin1);

How to perform type conversion in mysql

日期字符串转换函数

DATE_FORMAT(date, format)

将日期date按照给定的模式format转换成字符串。format中可使用以下模式元素
How to perform type conversion in mysql

How to perform type conversion in mysql

SELECT date_format(‘2009-10-04 22:23:00’, ‘%W %M %Y’);

How to perform type conversion in mysql

SELECT date_format(now(), ‘%W %M %Y’);

How to perform type conversion in mysql

SELECT date_format(‘2007-10-04 22:23:00’, ‘%H:%i:%s’);

How to perform type conversion in mysql

TIME_FORMAT(date, format)

format中只可使用时、分、秒和微秒模式元素

SELECT time_format(‘22:23:01’, ‘%H.%i.%s’);

How to perform type conversion in mysql

STR_TO_DATE(str, format)

将字符串str以指定的模式format转换成日期。format中可以包含模式元素和字面量,字面量必须匹配str中的字面量:

SELECT str_to_date(‘01,5,2013’,’%d,%m,%Y’);
SELECT str_to_date(‘May 1, 2013’,’%M %d,%Y’);

How to perform type conversion in mysql
以下在会话变量@@sql_mode设置中包含no_zero_date和no_zero_in_date时转换失败,没有包含这些设置时转换成功

SELECT str_to_date(‘a09:30:17’,‘a%h:%i:%s’);
 SELECT str_to_date(‘09:30:17a’,’%h:%i:%s’);

How to perform type conversion in mysqlHow to perform type conversion in mysql

【相关推荐:mysql视频教程

The above is the detailed content of How to perform type conversion in mysql. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn