Mysql中的varchar类型转换成int类型
1.实战案例
1.1 student表结构
mysql> desc student;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id | int(11)| YES || NULL | || name| varchar(20) | YES || NULL | || address | varchar(20) | YES || NULL | || score | varchar(3) | YES || NULL | || birthday | datetime | YES || NULL | || age| int(10)| YES || NULL | |+----------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)
注意都score我使用的是varchar类型，这里需要将其转换成int型。
1.2 插入数据
insert into student values(1,'lawson','',99,'1996-09-17',22);insert into student values(2,'ting','anhui_huaibei','','1996-09-17',22);
mysql> select * from student;+------+--------+---------------+-------+---------------------+------+| id | name | address | score | birthday | age |+------+--------+---------------+-------+---------------------+------+| 1 | lawson || 99 | 1996-09-17 00:00:00 | 22 || 2 | ting | anhui_huaibei | | 1996-09-17 00:00:00 | 22 |+------+--------+---------------+-------+---------------------+------+2 rows in set (0.00 sec)
1. 3 使用case when end句型 +cast强转
mysql> select-> id-> ,name-> ,case when score='' then 0-> else cast(score as signed) end score-> from student;+------+--------+-------+| id | name | score |+------+--------+-------+| 1 | lawson | 99 || 2 | ting |0 |+------+--------+-------+2 rows in set (0.03 sec)
对比没有使用cast语句
mysql> select id,name,score from student;+------+--------+-------+| id | name | score |+------+--------+-------+| 1 | lawson | 99 || 2 | ting | |+------+--------+-------+2 rows in set (0.00 sec)
2.注意事项
使用cast句型时，必须符合cast(score as signed)句型，这里的signed等价于int。但是不能写成cast(score as int)，否则会报错。但是singed并不是mysql中的基本类型，比如使用其创建表时会出错。mysql> create table t (id int);Query OK, 0 rows affected (0.22 sec)mysql> create table t1 (id signed);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 'signed)' at line 1
