Should I Quote Numbers in SQL

Should I quote numbers in SQL?

You should not quote numbers if you want to treat them as numbers.

You're correct by remembering that it makes it a string.

SELECT 10 AS x

is perfectly legal and will return (in most database engines) a column of datatype int (or a variation thereof.)

If you do this:

SELECT '10' AS x

instead you'll get a textual data type. This might too be suitable in some cases, but you need to decide whether you want the result as text or as a number.

When to use single quotes in an SQL statement?

Single quotes (') denote textual data, as you noted (e.g., 'Mike' in your example). Numeric data (e.g., 3 in your example), object (table, column, etc) names and syntactic elements (e.g., update, set, where) should not be wrapped in quotes.

When to use single quotes, double quotes, and backticks in MySQL

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

Single quotes should be used for string values like in the VALUES() list. Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double.

MySQL also expects DATE and DATETIME literal values to be single-quoted as strings like '2001-01-01 00:00:00'. Consult the Date and Time Literals documentation for more details, in particular alternatives to using the hyphen - as a segment delimiter in date strings.

So using your example, I would double-quote the PHP string and use single quotes on the values 'val1', 'val2'. NULL is a MySQL keyword, and a special (non)-value, and is therefore unquoted.

None of these table or column identifiers are reserved words or make use of characters requiring quoting, but I've quoted them anyway with backticks (more on this later...).

Functions native to the RDBMS (for example, NOW() in MySQL) should not be quoted, although their arguments are subject to the same string or identifier quoting rules already mentioned.

Backtick (`)
table & column ───────┬─────┬──┬──┬──┬────┬──┬────┬──┬────┬──┬───────┐
                      ↓     ↓  ↓  ↓  ↓    ↓  ↓    ↓  ↓    ↓  ↓       ↓
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`, `updated`) 
                       VALUES (NULL, 'val1', 'val2', '2001-01-01', NOW())";
                               ↑↑↑↑  ↑    ↑  ↑    ↑  ↑          ↑  ↑↑↑↑↑ 
Unquoted keyword          ─────┴┴┴┘  │    │  │    │  │          │  │││││
Single-quoted (') strings ───────────┴────┴──┴────┘  │          │  │││││
Single-quoted (') DATE    ───────────────────────────┴──────────┘  │││││
Unquoted function         ─────────────────────────────────────────┴┴┴┴┘    

Variable interpolation

The quoting patterns for variables do not change, although if you intend to interpolate the variables directly in a string, it must be double-quoted in PHP. Just make sure that you have properly escaped the variables for use in SQL. (It is recommended to use an API supporting prepared statements instead, as protection against SQL injection).

// Same thing with some variable replacements
// Here, a variable table name $table is backtick-quoted, and variables
// in the VALUES list are single-quoted 
$query = "INSERT INTO `$table` (`id`, `col1`, `col2`, `date`) VALUES (NULL, '$val1', '$val2', '$date')";

Prepared statements

When working with prepared statements, consult the documentation to determine whether or not the statement's placeholders must be quoted. The most popular APIs available in PHP, PDO and MySQLi, expect unquoted placeholders, as do most prepared statement APIs in other languages:

// PDO example with named parameters, unquoted
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (:id, :col1, :col2, :date)";

// MySQLi example with ? parameters, unquoted
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (?, ?, ?, ?)";

Characters requring backtick quoting in identifiers:

According to MySQL documentation, you do not need to quote (backtick) identifiers using the following character set:

ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

You can use characters beyond that set as table or column identifiers, including whitespace for example, but then you must quote (backtick) them.

Also, although numbers are valid characters for identifiers, identifiers cannot consist solely of numbers. If they do they must be wrapped in backticks.

What is the difference between single and double quotes in SQL?

Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren't used in SQL, but that can vary from database to database.

Stick to using single quotes.

That's the primary use anyway. You can use single quotes for a column alias — where you want the column name you reference in your application code to be something other than what the column is actually called in the database. For example: PRODUCT.id would be more readable as product_id, so you use either of the following:

• SELECT PRODUCT.id AS product_id
• SELECT PRODUCT.id 'product_id'

Either works in Oracle, SQL Server, MySQL… but I know some have said that the TOAD IDE seems to give some grief when using the single quotes approach.

You do have to use single quotes when the column alias includes a space character, e.g., product id, but it's not recommended practice for a column alias to be more than one word.

SQL Single quote and double quote inserting values

Always use single quote, and double single quote when needed.

('Where I''m Calling From: Selected Stories', 'Raymond', 'Carver', 1989, 12, 526)

What effect do quotes have when placed around numbers in SELECT statements?

In terms of performance they do not matter for mysql. For when to use them you can see this question When to use single quotes, double quotes, and backticks in MySQL

You are most likely experiencing a warmed up buffer pool or query caching. Also, when I locally query a remote a sql machine in amazon I can have variability in response time just from my own network performance and remote machine load. If you want to really test it out try profiling the query.

http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

For this specific case it is an eccentricity of mysql that you cannot use a string type index when querying for a numeric value. The reverse however works.

Should you quote numeric in MySQL requests?

I would strongly suggest to be carefull when quoting numerical values - here is what happened to me: An optimized, often-run query produced insane amounts of IO and quite some CPU laod:

SELECT blah FROM foo WHERE intcolumn='17';

with the selectivity being some 100 rows out of millions. I checked the execution plan: lo and behold, full table scan on the driving table. I checked the index on foo(intcolumn) again and again, even dropped and recreated it, no luck. Query time was in the minutes.

SELECT blah FROM foo WHERE intcolumn=17;

took less than 0.1 seconds. For some reason, MySQL had chosen to cast all foo.intcolumn to VARCHAR and then do a string compare to '17'. Ofcourse this included ignoring the index.

I don't know, if I hit an exotic bug in an old version of MySQL, bu I surely took away one thing: Make sure, the parser knows, what data type I intend to use. This ofocurse can be tricky with quoted numerals.

---

Related Topics