Question 21390 in Mysql Question posted in
The official MySQL documentation can be found here.

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search