Mysql中的varchar类型转换成int类型

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
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页