mysql字段类型后为啥加数字_为什么可以在MySQL中使用数字字符串值设置整数字段，并且此功能始终有效吗？...

I just noticed that phpMyAdmin for some reason always seems to use numeric string values instead of integer values (i.e. '5' instead of 5) in its generated SQL queries for writing data to integer fields in MySQL databases, which I did not know was even valid SQL.

This made me curious, so I did some follow-up experiments myself, and could confirm that this is indeed allowed, even in DDL queries like:

ALTER TABLE MyTable ALTER COLUMN MyIntField SET DEFAULT '5'

(where, possibly needless to say, the "MyIntField" is of the "int" type)

and also in normal insert and update queries like:

INSERT INTO MyTable (MyIntField) VALUES ('5');

UPDATE MyTable SET MyIntField = '5' WHERE id = 1;

How/why is this allowed by MySQL? Databases normally care about types, and strings and integers are definitely different types?

Also, with this "feature", the same SQL injection sanitation procedure could be used for both string values and integer values (instead of integer validation testing input values for integer fields and quote-escape-testing input values for string fields separately), so my follow-up question is: Can I depend on the existence and support of this feature under all conditions in MySQL, and also in future versions of MySQL, i.e. is it documented somewhere officially?

解决方案

MySQL is pretty lax about most things, by default fixing these errors by doing everything it can to not error out.

MySQL does have a strict mode though which would not allow that behaviour

For more info.

This 'feature' can also cause performance issues, comparing a sting to an index'ed int makes the index useless (in some cases, sometimes the optimiser can spot this and cast only the input)

Strict SQL Mode

Strict mode controls how MySQL handles invalid or missing values in

data-change statements such as INSERT or UPDATE. A value can be

invalid for several reasons. For example, it might have the wrong data

type for the column, or it might be out of range. A value is missing

when a new row to be inserted does not contain a value for a non-NULL

column that has no explicit DEFAULT clause in its definition. (For a

NULL column, NULL is inserted if the value is missing.) Strict mode

also affects DDL statements such as CREATE TABLE.