首页 / MYSQL / mysql - varchar类型与数字的比较和转换

mysql - varchar类型与数字的比较和转换

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql - varchar类型与数字的比较和转换，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4571字，纯文字阅读大概需要7分钟。

内容图文

mysql - varchar类型与数字的比较和转换

convert(对象, 目标类型)是mysql中常用的类型转换对象函数。eg: select convert(‘1.123’, decimal(10.4))，结果就是1.1230。对象可以用列名替代。

前两天发现，一个小伙伴之前设计表时把某个表数据类型设计成了varchar，实际用于存储Decimal。我需要用其数据进行过滤筛选

如果文章内容有问题，欢迎评论或与我进行讨论（请注明原因）：

mail: wgh0807@qq.com
微信: hello-wgh0807
qq: 490536401

结论

使用convert或直接进行数字比较，在字符串与数字进行比较时，先对字符串进行了解析，遇到非数字类型(即字母等)会停止识别，然后将成功解析的部分（从左到右，异常为止）识别转换为数字。如‘1.56a8d7‘ -> 1.56，‘abcdefg...‘->0。

若确定数据实际上是数字类型，可以使用数字进行比较，如column>1.1。

如果用到将数字存储为字符串，需要转换回数字或需要进行比较的操作，推荐使用convert方法。CONVERT(column, decimal(10,5))，数据类型可以按照实际需要修改。

发现问题

我一直习惯于在idea中Database console 下直接编写sql语句完成所有对数据库的操作，这次也是，我使用了以下sql进行查询：select * from test_table where volume > 0 。由于数据量较大，并没有发现什么问题，过滤掉了0.0000，效率还不错。

mysql - varchar类型与数字的比较和转换 - 文章图片

可是组长大大喜欢用navicat，毕竟可视化，查询、修改也方便。于是我也下载了一个，直观的修改条件，查看结果。但却筛选到出了错误的结果（如下图）：

mysql - varchar类型与数字的比较和转换 - 文章图片

从图中我们可以看到，在navicat的shaix中以volume>0，’0.00’竟然成功通过筛选！我当时就怀疑sql设计的问题

分析测试

这里需要注意，我直接构建sql筛选用到的是整型的0，在sql语句中为 volume> 0，但是使用navicat时，由于表项类型是varchar，故其经过识别，在组装成sql的时候变成volumn > ‘0‘，在比较过程中采用的是字符串的比较，故‘0.0‘>‘0‘。

目测没啥问题了，但是心虚的我又编写了一个测试sql，用于计数：

select count(*) from (SELECT * FROM lh_dc_bond_price_net WHERE dq_volume > 0) a where dq_volume like ‘0.00%‘;

计数结果为空，我放心了。突然小伙伴-霖提问：如果出现字母会怎么样呢？可以正确识别吗？识别正确的部分？还是会报错？

我做了个实验：

select '1.23a>1.23','1.23a'>1.23 UNION select '1.23a<1.23','1.23a'<1.23 UNION select '1.23a=1.23','1.23a'=1.23;

SELECT '1a.23>1','1a.23'> 1 UNION SELECT '1a.23<1','1a.23'< 1 UNION SELECT '1a.23=1','1a.23'=1;

SELECT 'a>0','a'>0 UNION SELECT 'a<0','a'<0 UNION SELECT 'a=0','a'=0;

三次结果都是0,0,1。由此可得，在字符串与数字进行比较时，先对字符串进行了解析，将成功解析的部分（从左到右，异常为止）识别转换为数字。 此处和convert转换结果相同。

小结：若需要进行比较，则可以直接让字符串与数字进行比较。若需要获得对象转换后的值，则需要使用convert函数或cast进行转换。

mysql 5.7 数据类型转换方法总结

此部分主要参考mysql参考文档 - 12.10 Cast Functions and Operators

常用的转换方法：BINARY、CAST、CONVERT。

  • BINARY expr将对象转换为二进制格式，常用语区分大小写和检测空格。

  • CAST(expr AS type)：接收任意类型的表达式并生成指定类型的结果值，功能和语法类似于CONVERT。是标准SQL语法。
  • Convert(expr,type) 或Convert(expr using transcoding_name)。可以采用任何类型表达式生成指定类型的结果值。
    • Convert(expr,type)等价于CAST(expr AS type)函数。
    • Convert(expr using transcoding_name) 是标准SQL语法。在不同编码格式下转换

convert未使用using 和 cast 时，type的类型可以包括以下值

  • BINARY[(N)]

    生成具有BINARY数据类型的字符串 。有关如何影响比较的说明，请参见 第11.4.2节“BINARY和VARBINARY类型”。如果N给出了可选长度 ，则 会使强制转换使用不超过 参数的字节。短于字节的值用字节填充到长度为 。 BINARY(N)NN0x00N

  • CHAR[(N)] [charset_info]

    生成具有CHAR数据类型的字符串 。如果N给出了可选长度，则会使强制转换使用不超过 参数的字符。对于短于字符的值，不会出现填充 。

    如果没有charset_info子句，则 CHAR生成具有默认字符集的字符串。要明确指定字符集，charset_info允许使用以下值：

    • CHARACTER SET charset_name：生成具有给定字符集的字符串。

    • ASCII：简写 CHARACTER SET latin1。

    • UNICODE：简写 CHARACTER SET ucs2。

    在所有情况下，字符串都具有字符集的默认排序规则。

  • DATE

    产生DATE值。

  • DATETIME

    产生DATETIME值。

  • DECIMAL[(M[,D])]

    产生DECIMAL值。如果给出了可选值M和 D值，则它们指定最大位数（精度）和小数点后面的位数（刻度）。

  • JSON （在MySQL 5.7.8中添加）

    产生JSON值。有关在JSON其他类型之间转换值的规则的详细信息 ，请参阅 JSON值的比较和排序。

  • NCHAR[(N)]

    类似CHAR，但会产生一个带有国家字符集的字符串。请参见 第10.3.7节“国家字符集”。

    与CHAR不同，NCHAR不允许指定尾随字符集信息。

  • SIGNED [INTEGER]

    生成有符号整数值。

  • TIME

    产生TIME价值。

  • UNSIGNED [INTEGER]

    生成无符号整数值。

更多信息请见 mysql 参考手册

参考文档：

MySQL中字符串与数字比较的坑 - Live In A Dream

mysql参考文档 - 12.3.2 Comparison Functions and Operators

[mysql参考文档 - 12.10 Cast Functions and Operators](

mysql - varchar类型与数字的比较和转换

标签：tor   过程   def   了解   cti   block   sql语句   添加   二进制格式   

本文系统来源：https://www.cnblogs.com/wgh0807/p/11432320.html

内容总结

以上是互联网集市为您收集整理的mysql - varchar类型与数字的比较和转换全部内容，希望文章能够帮你解决mysql - varchar类型与数字的比较和转换所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/511798.html

来源：【匿名】