mysql自动将字符串转换为整数 [英] mysql automatically cast strings to integer

问题描述

我刚刚注意到，如果我执行这样的MySQL请求:

I've just noticed that if I do a MySQL request like this one:

SELECT 1 FROM myTable WHERE id = 'asdf'

然后将字符串'asdf'强制转换为0. 这意味着我有一个ID为0的记录，该记录将匹配. id字段的格式为int(8).

Then the string 'asdf' is casted to 0. It means that I have a record with id 0 this will match. The format of the id field is int(8).

什么是最好的进行方式?

What is the best way to proceed:

• 我需要检查(例如，通过PHP)我的值仅是数字吗?
• 有MySQL的方法吗?
• 我必须删除ID为0的记录吗? (不好)

• I need to check (by PHP for example) that my value is numerical only?
• There is a MySQL way to do that?
• I must remove my record with id 0? (bad)

推荐答案

您必须首先通过PHP清理输入.

You must first sanitize your inputs via PHP.

$id = 'asdf';
if(is_numeric($id)){
    $query("SELECT 1 FROM myTable WHERE id = $id");
}else{
    die("ID is not numeric");
}

或者您可以这样做:

    SELECT 1 FROM myTable WHERE id = 'asdf' AND 'asdf' REGEXP '^-?[0-9]+$'

这将导致正则表达式= false，导致不返回任何行.

This would cause the regex to = false, causing no rows to return.

这篇关于mysql自动将字符串转换为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！