MySQL inconsistently requires quote marks around varchar inputs-CodePudding

In my "merchants" table, column AXPID is of integer data type, Vendor is varchar(255) and StoreNum is varchar(20).

at the mysql command line, I entered

INSERT INTO merchants (AXPID, Vendor, Storenum) VALUES (3, Target, 1911);

and got "ERROR 1054 (42S22): Unknown column 'Target' in 'field list'. So I entered

INSERT INTO merchants (AXPID, Vendor, Storenum) VALUES (3, 'Target', 1911);

and got the good ol' "Query OK, 1 row affected (0.01) sec" message. SELECT * FROM merchants; confirmed that the data I want was in fact inserted.

What's puzzling me is why didn't the absence of quote marks round 1911 cause an error; when this is what caused an error when entering Target and both of those data are going into columns whose datatype is varchar?

(stack overflow seemed to require me to say this: I tried it without quote marks round Target or 1911 and got an error; then tried it again with quote marks round Target and expected to get the same 1054 error for not having quote marks round 1911 and instead no error occurred.

CodePudding user response：

A string without quote marks is interpreted as an identifier (i.e. a column name).

A number literal without quote marks is a value. See https://dev.mysql.com/doc/refman/8.0/en/number-literals.html

MySQL performs automatic type casting, so a number can be used in a string context, and it becomes as if you had put it in quotes.

https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts strings to numbers as necessary, and vice versa.

CodePudding user response：

My guess would point to the SQL query compiler -

Often in programming languages a variable name/token cannot start with a number. So Target would compile to a variable/entity/token named "Target", but 1911 can only be mapped to a constant number, and is then auto converted to the VARCHAR representation.

This excerpt from mysql documentation speaks to this:

Identifiers may begin with a digit but unless quoted may not consist solely of digits.