Mysql - 你能在一种情况下检查空白字符串和 0 吗?
我想检查 mysql 中是否有一列为空,即 '' 或 0。 有没有办法在一个条件下做到这一点? 就像
- WHERE order_id > ''
或
- WHERE order_id != ''
这两种方法都有效,还是有不同的解决方案?
I want to check in mysql if a column is either blank, ie '', or 0.
Is there a way to do this with one condition?
Like
- WHERE order_id > ''
or
- WHERE order_id != ''
Would either of these work, or is there a different solution?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
这是否符合条件之一?
Does this qualify as one condition?
这更多的是数据质量的问题。在设计良好的数据库中,
''
和0
之间应该有相当明显的区别。如果您对此含糊其辞,那么除了这两个值之外,还有很多值可以被解释为“空白”。
NULL
是显而易见的,但是空白呢?或者包含0.00
的字符串,或者即使您正在查找数值,也可以是任何非数字字符串。理想情况下,数据应该以与其应保存的数据类型相匹配的格式存储 - 例如,如果您需要一个数字字段,它应该是
int
或其他数字类型,具体取决于您要存储的内容。这样,它就永远不会包含字符串值,因此您永远不需要检查它。如果您无法更改数据库本身的类型,则下一个最佳解决方案是将值转换为您在选择查询中期望的数据类型。例如:
有关更多信息,请参阅 MySQL 手册: http://dev .mysql.com/doc/refman/5.0/en/cast-functions.html
但是,最终,它确实取决于您期望数据字段包含的内容以及您希望如何反应不同类型的内容。
This is more a question of data quality. In a well designed database, there should be a fairly clear-cut difference between
''
and0
.If you're being vague about it, there are quite a lot of values that could be interpreted as "blank" in addition to these two.
NULL
is the obvious one, but what about white space? Or a string containing0.00
, or even if you're looking for a numeric value, any non-numeric string.Ideally, the data should be stored in a format that matches the type of data it is supposed to hold - for example, if you're expecting a numeric field, it should be an
int
, or another numeric type, depending on exactly what you want to store. That way, it can never contain a string value, so you would never need to check for it.If you can't change the type in the DB itself, the next best solution is to cast the value as that data type you are expecting in the select query. eg:
See the MySQL manual for more info: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
However, in the end, it does depend on exactly what you are expecting the data field to contain, and how you want to react to different types of content.
我尝试了一下,似乎答案是:
这将显示 order_id 不为 0 且也不为空的结果
I experimented a bit and it seems the answer is:
This will show results where order_id is not 0 and also not blank
为什么你不使用简单查询来测试你的两个条件
select * from tbl_name where order_id=' ' or order_id = 0
尝试这个它会起作用
why dont u use a smiple query where both of ur conditions are going to be tested
select * from tbl_name where order_id=' ' or order_id = 0
try this it will work