mysqlvarcharmysql-error-1054

MySQL inconsistently requires quote marks around varchar inputs


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.


    Re your comment:

    how to reconcile MySQL's inclination to view the unquoted string 'Target' as an identifier, when it is most indisputable enclosed within the VALUES clause of the statement?

    By remembering this simple rule: In SQL, a string literal must be quoted. This is true in any context where you use strings in SQL. If it isn't quoted, then it's not a string literal. It may be an identifier, or a numeric literals, or an SQL keyword or operator.

    If you didn't need quotes around string literals sometimes, depending on where you use them, then that would be inconsistent.

    Every other programming language works this way, too. There are fixed rules for what constitutes different language elements. Programming languages have rather rigid grammar rules, for the purpose that the code that parses them needs to be efficient and reliable.