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.
>Solution :
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.