mysql - 引用数字与否？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6781976/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql - quote numbers or not?
提问by Stann
For example - I create database and a table from cli and insert some data:
例如 - 我从 cli 创建数据库和一个表并插入一些数据：
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 stringthat actually stored as INT in mysql and then I inserted a stringin a column that is INT.
所以 - 在这些示例中，我通过在 mysql 中实际存储为 INT的字符串选择了一行，然后在 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?
我不太明白为什么这在这里工作的方式。为什么允许在 INT 列中插入字符串？
Can I insert all Mysql data types as strings?
我可以将所有 Mysql 数据类型作为字符串插入吗？
Is this behavior standard across different RDBMS?
这是跨不同 RDBMS 的行为标准吗？
thanks!
谢谢！
回答by Marc B
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.
MySQL 很像 PHP，并且会尽可能地自动转换数据类型。由于您使用的是 int 字段（左侧），因此它也会尝试将参数的右侧透明地转换为 int，因此'9'就变成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.
严格来说，引号是不必要的，并且强制 MySQL 进行类型转换/转换，因此会浪费一点 CPU 时间。实际上，除非您运行的是 Google 规模的操作，否则这种转换开销将非常小。
回答by Sean
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.
真正的问题归结为类型转换。当您将数字放在引号内时，它被视为字符串，MySQL 必须将其转换为数字才能执行查询。虽然这可能需要一点时间，但当 MySQL 不能很好地转换字符串时，真正的问题就开始出现了。例如，MySQL 会将基本字符串（如“123”）转换为整数 123，但会将一些较大的数字（如“18015376320243459”）转换为浮点数。由于浮点数可以四舍五入，您的查询可能会返回不一致的结果。在此处了解有关类型转换的更多信息。根据您的服务器硬件和软件，这些结果会有所不同。MySQL解释了这一点。
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)
如果您担心 SQL 注入，请始终先检查值并使用 PHP 去除任何非数字。您可以为此使用 preg_replace：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.
此外，如果您使用引号编写 SQL 查询，它们将不适用于 PostgreSQL 或 Oracle 等数据库。
回答by RolandoMySQLDBA
This is not standard behavior.
这不是标准行为。
For MySQL 5.5. this is the default SQL Mode
对于 MySQL 5.5。这是默认的 SQL 模式
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 setIF AND ONLY IFyou want to make the SQL more ANSI-compliant. Otherwise, you don't have to touch a thing. I've never done so.
Oracle 和 PostgreSQL 更严格地使用 ANSI 和 TRADITIONAL。如果您想让 SQL 更符合 ANSI 标准，则必须设置 MySQL 允许的 SQL 模式。否则，您不必触摸任何东西。我从来没有这样做过。
回答by Frank Schmitt
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)
AFAIK 这是标准的，但它被认为是不好的做法，因为
- 在 WHERE 子句中使用它会阻止优化器使用索引（解释计划应该显示）
- 数据库必须做额外的工作才能将字符串转换为数字
- 如果您将它用于浮点数（'9.4'），如果客户端和服务器使用不同的语言设置（9.4 vs 9,4），您会遇到麻烦
In short: don't do it (but YMMV)
简而言之：不要这样做（但 YMMV）
回答by Srinivas Kishore
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)
回答by Anush
You don't need to quote the numbers but it is always a good habit if you do as it is consistent.
你不需要引用数字，但如果你这样做是一致的，这总是一个好习惯。
回答by pine3ree
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 usernameIS NOT NULL.
在 mysql/maria-db 中，您将获得所有usernameIS NOT NULL的记录。
Always quote values if the column is of type string (char, varchar,...) otherwise you'll get unexpected results!
如果列的类型是字符串（char、varchar、...），则始终引用值，否则您会得到意想不到的结果！
回答by Tamer
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:
问题是，假设我们有一个名为 users 的表，其中有一列名为 current_balance 的 FLOAT 类型，如果您运行以下查询：
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:
current_balance 字段将更新为 231608，因为 MySQL 进行了四舍五入，类似地，如果您尝试以下查询：
UPDATE `users` SET `current_balance`='231608.55' WHERE `user_id`=9;
The current_balance field will be updated to 231609
current_balance 字段将更新为 231609
