In MySQL, should I quote numbers or not?

Question

For example - I create database and a table from cli and insert some data:

CREATE DATABASE testdb CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
USE testdb;
CREATE TABLE test (id INT, str VARCHAR(100)) TYPE=innodb CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
INSERT INTO test VALUES (9, 'some string');

Now I can do this and these examples do work (so - quotes don't affect anything it seems):

SELECT * FROM test WHERE id = '9';
INSERT INTO test VALUES ('11', 'some string');

So - in these examples I've selected a row by a string that actually stored as INT in mysql and then I inserted a string in a column that is INT.

I don't quite get why this works the way it works here. Why is string allowed to be inserted in an INT column?

Can I insert all MySQL data types as strings?

Is this behavior standard across different RDBMS?

Answer 1

MySQL is a lot like PHP, and will auto-convert data types as best it can. Since you're working with an int field (left-hand side), it'll try to transparently convert the right-hand-side of the argument into an int as well, so '9' just becomes 9.

Strictly speaking, the quotes are unnecessary, and force MySQL to do a typecasting/conversion, so it wastes a bit of CPU time. In practice, unless you're running a Google-sized operation, such conversion overhead is going to be microscopically small.

Answer 2

You should never put quotes around numbers. There is a valid reason for this.

The real issue comes down to type casting. When you put numbers inside quotes, it is treated as a string and MySQL must convert it to a number before it can execute the query. While this may take a small amount of time, the real problems start to occur when MySQL doesn't do a good job of converting your string. For example, MySQL will convert basic strings like '123' to the integer 123, but will convert some larger numbers, like '18015376320243459', to floating point. Since floating point can be rounded, your queries may return inconsistent results. Learn more about type casting here. Depending on your server hardware and software, these results will vary. MySQL explains this.

If you are worried about SQL injections, always check the value first and use PHP to strip out any non numbers. You can use preg_replace for this: preg_replace("/[^0-9]/", "", $string)

In addition, if you write your SQL queries with quotes they will not work on databases like PostgreSQL or Oracle.

Answer 3

Check this, you can understand better ...

mysql> EXPLAIN SELECT COUNT(1) FROM test_no WHERE varchar_num=0000194701461220130201115347;
+----+-------------+------------------------+-------+-------------------+-------------------+---------+------+---------+--------------------------+
| id | select_type | table                  | type  | possible_keys     | key                  | key_len | ref  | rows    | Extra                    |
+----+-------------+------------------------+-------+-------------------+-------------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | test_no | index | Uniq_idx_varchar_num | Uniq_idx_varchar_num | 63      | NULL | 3126240 | Using where; Using index |
+----+-------------+------------------------+-------+-------------------+-------------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(1) FROM test_no WHERE varchar_num='0000194701461220130201115347';
+----+-------------+------------------------+-------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table                  | type  | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+------------------------+-------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test_no | const | Uniq_idx_varchar_num | Uniq_idx_varchar_num | 63      | const |    1 | Using index |
+----+-------------+------------------------+-------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> SELECT COUNT(1) FROM test_no WHERE varchar_num=0000194701461220130201115347;
+----------+
| COUNT(1) |
+----------+
|        1 |
+----------+
1 row in set, 1 warning (7.94 sec)

mysql> SELECT COUNT(1) FROM test_no WHERE varchar_num='0000194701461220130201115347';
+----------+
| COUNT(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Answer 4

This is not standard behavior.

For MySQL 5.5. this is the default SQL Mode

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

ANSI and TRADITIONAL are used more rigorously by Oracle and PostgreSQL. The SQL Modes MySQL permits must be set IF AND ONLY IF you want to make the SQL more ANSI-compliant. Otherwise, you don't have to touch a thing. I've never done so.

Answer 5

AFAIK it is standard, but it is considered bad practice because
- using it in a WHERE clause will prevent the optimizer from using indices (explain plan should show that)
- the database has to do additional work to convert the string to a number
- if you're using this for floating-point numbers ('9.4'), you'll run into trouble if client and server use different language settings (9.4 vs 9,4)

In short: don't do it (but YMMV)

Answer 6

It depends on the column type! if you run

SELECT * FROM `users` WHERE `username` = 0;

in mysql/maria-db you will get all the records where username IS NOT NULL.

Always quote values if the column is of type string (char, varchar,...) otherwise you'll get unexpected results!

Answer 7

The issue is, let's say that we have a table called users, which has a column called current_balance of type FLOAT, if you run this query:

UPDATE `users` SET `current_balance`='231608.09' WHERE `user_id`=9;

The current_balance field will be updated to 231608, because MySQL made a rounding, similarly if you try this query:

UPDATE `users` SET `current_balance`='231608.55' WHERE `user_id`=9;

The current_balance field will be updated to 231609

Answer 8

You don't need to quote the numbers but it is always a good habit if you do as it is consistent.