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.
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)
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);
SELECT cast(‘28.82abc’ AS DECIMAL(4));
If 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));
, 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));
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);
SELECT cast(’-28abc’ AS SIGNED);
SELECT cast(‘2007-12-25’ AS DATETIME);
SELECT cast(‘2007-12-25’ AS DATE);
SELECT cast(‘20:20:20’ AS TIME);
The date format must be 'YYYY-MM-DD'
SELECT cast(‘25-12-2007’ AS DATETIME); SELECT cast(‘2007-may-25’ AS DATETIME);
SELECT cast(123 AS CHAR); SELECT cast(123 AS BINARY);
SELECT cast(now() AS CHAR);
二进制字符串：二进制字符串被视为一个连续的字节序列，与字符集无关。非二进制字符串（即我们通常所说的字符串）被视为一个连续排列的字符序列，与字符集有关。所谓与字符集无关，是指与MySQL自己的字符集无关，而是按照操作系统的字符集把字符串转换成字节进行存储
两种字符串的比较方式：二进制字符串的比较方式是一个字节一个字节进行的，比较的依据是两个字节的二进制值。因为同一个字母的大小写的数值编码是不一样的，因此它是区分大小写的。另外，由于它和字符集无关，因此也就没有大写和小写字母一说
非二进制字符串的比较方式是一个字符一个字符进行的，比较的依据是两个字符在字符集中的先后顺序。根据使用的校对不同，可以进行区分大小写的比较和不区分大小写的比较
使用CAST(str AS BINARY)将字符串转换成二进制字符串时，通常使用它的快捷方式写法： BINARY str
查看当前字符集和校对规则设置
SHOW VARIABLES LIKE ‘collation_%’;
在比较表达式中，binary影响后面所有的字符串，并且不会忽略字符串的尾部空格
SELECT ‘a’ = ‘A’ ,BINARY ‘a’ = ‘A’; SELECT ‘a’ = 'a ',BINARY ‘a’ = 'a ';
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);
SELECT convert(‘你好’ USING gbk);
如果目标字符集不能表示该字符，则返回乱码
SELECT convert(‘你好’ USING latin1);
日期字符串转换函数
DATE_FORMAT(date, format)
将日期date按照给定的模式format转换成字符串。format中可使用以下模式元素
SELECT date_format(‘2009-10-04 22:23:00’, ‘%W %M %Y’);
SELECT date_format(now(), ‘%W %M %Y’);
SELECT date_format(‘2007-10-04 22:23:00’, ‘%H:%i:%s’);
TIME_FORMAT(date, format)
format中只可使用时、分、秒和微秒模式元素
SELECT time_format(‘22:23:01’, ‘%H.%i.%s’);
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’);
以下在会话变量@@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’);
【相关推荐：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!