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

最新推荐文章于 2025-06-18 21:17:26 发布

说文科技

最新推荐文章于 2025-06-18 21:17:26 发布

阅读量9.3k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Mysql

喜欢文章？请私信联系作者。

本文链接：https://blog.youkuaiyun.com/liu16659/article/details/83617743

Mysql 专栏收录该内容

122 篇文章

订阅专栏

本文通过实战案例，演示了如何在Mysql中将varchar类型的字段转换为int类型，特别关注了score字段的转换过程，并提供了使用case when end句型结合cast强转的具体方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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