严格模式以避免类型转换

Is there any sql mode that will return an error instead of implicitly converting the string to integer?

是否有任何sql模式将返回错误而不是隐式将字符串转换为整数？

mysql> select * from todel ;
+------+--------+
| id   | name   |
+------+--------+
|    1 | abc    |
|    2 | xyz    |
|    0 | ABCxyz |
+------+--------+
3 rows in set (0.00 sec)

I expect an error message instead of a row with id 0

我期待一条错误消息而不是id为0的行

mysql> select * from todel where id = 'abc';
+------+--------+
| id   | name   |
+------+--------+
|    0 | ABCxyz |
+------+--------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'abc' |
+---------+------+-----------------------------------------+
1 row in set (0.01 sec)

2 个解决方案

#1

I understand your concerns, but it's for this very reason you should never have an id set to 0. In the long run I think you should reconsider your table rows before the behavior which isn't a problem in ideal situations. I haven't found anything relevant to this through a little searches, and that's probably because it's probably not a problem unless you make it one.

我理解你的担忧，但正是由于这个原因，你永远不应该将id设置为0.从长远来看，我认为你应该在行为之前重新考虑你的表行，这在理想情况下不是问题。我没有通过一些搜索找到与此相关的任何内容，这可能是因为它可能不是问题，除非你把它做成一个。

Apart from that, you could read relevant column data and act accordingly in php/whatev. From the table COLUMNS in information_schema, you can filter by TABLE_SCHEMA (database), TABLE_NAME and COLUMN_NAME to get DATATYPE (double). If the column you're changing has a certain DATATYPE, let the script give error before running the MySQL query.

除此之外，您可以阅读相关的列数据并在php / whatev中进行相应的操作。从information_schema中的表COLUMNS，您可以按TABLE_SCHEMA（数据库），TABLE_NAME和COLUMN_NAME进行筛选，以获取DATATYPE（double）。如果您要更改的列具有某个DATATYPE，请在运行MySQL查询之前让脚本出错。

Another way to do it would simply be to convert input before parsing:

另一种方法是在解析之前转换输入：

if ( ! is_numeric($id))
    $id = 'NULL';

To prevent incorrect INSERTs or UPDATEs, you already have that mode.

为了防止错误的INSERT或UPDATE，您已经拥有该模式。

In the end I can't come up with many practical ways that this strict mode you're after would benefit the MySQL users.

最后，我无法想出许多实际的方法，你所遵循的这种严格模式将使MySQL用户受益。

#2

You can use STRICT_ALL_TABLES sql mode:

您可以使用STRICT_ALL_TABLES sql模式：

set @@GLOBAL.sql_mode  = "STRICT_ALL_TABLES";
set @@SESSION.sql_mode = "STRICT_ALL_TABLES";

However it works just on write operations:

但它只适用于写操作：

MariaDB [(none)]> insert into test.test values ( "abc", "lol" );
--------------
insert into test.test values ( "abc", "lol" )
--------------

ERROR 1366 (22007): Incorrect integer value: 'abc' for column 'id' at row 1

There is no such thing to disable implicit conversions for read queries; instead you can just check if there are warnings and if yes, just free the result, abort the statement, and threat those warnings as errors.

没有这样的事情可以禁用读取查询的隐式转换;相反，你可以检查是否有警告，如果是，只需释放结果，中止语句，并将这些警告威胁为错误。

#1