在MySQL中，我应该引用数字或不

例如，我从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');

现在我可以这样做，这些例子确实有效(所以-引号不会影响任何事情):

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

所以-在这些例子中，我选择了一行字符串，实际上存储为INT在mysql中，然后我插入了一个字符串在一个列是INT。

我不太明白为什么它是这样工作的。为什么允许在INT列中插入字符串?

我可以插入所有MySQL数据类型作为字符串吗?

这种行为是跨不同RDBMS的标准吗?

MySQL很像PHP，并且会尽可能地自动转换数据类型。由于您正在处理int字段(左侧)，它将尝试透明地将参数的右侧也转换为int，因此'9'就变成了9。

严格来说，引号是不必要的，并且会强制MySQL进行类型转换/转换，因此会浪费一点CPU时间。在实践中，除非您正在运行一个google大小的操作，否则这样的转换开销将是非常小的。

千万不要用引号括住数字。这是有正当理由的。

真正的问题归结为类型转换。当你把数字放在引号内时，它被视为字符串，MySQL必须在执行查询之前将其转换为数字。虽然这可能会花费很少的时间，但是当MySQL不能很好地转换字符串时，真正的问题就开始出现了。例如，MySQL将基本字符串(如'123')转换为整数123，但会将一些更大的数字(如'18015376320243459')转换为浮点数。由于浮点数可以舍入，因此查询可能返回不一致的结果。在这里了解更多关于类型转换的信息。根据服务器硬件和软件的不同，这些结果会有所不同。

如果你担心SQL注入，总是先检查值，然后用PHP去掉任何非数字。您可以使用preg_replace: preg_replace("/[^0-9]/", "", $string)

另外，如果你写的SQL查询带有引号，它们将无法在PostgreSQL或Oracle等数据库上工作。

检查这个，你可以更好地理解…

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)

当然这是标准的，但它被认为是不好的做法，因为
在WHERE子句中使用它会阻止优化器使用索引(explain plan应该显示)
-数据库必须做额外的工作来将字符串转换为数字
-如果你用它来处理浮点数('9.4')，如果客户端和服务器使用不同的语言设置(9.4 vs 9,4)，你会遇到麻烦

总之:不要这样做(但是YMMV)

这不是标准行为。

MySQL 5.5。这是默认的SQL模式

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

Oracle和PostgreSQL更严格地使用

ANSI和TRADITIONAL。当且仅当要使SQL更符合ansi时，必须设置MySQL允许的SQL模式。否则，你什么都不用碰。我从来没有这样做过。

这取决于列的类型!如果你运行

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

在mysql/maria-db中，你会得到所有username不是NULL的记录。

如果列是字符串类型(char, varchar，…)，则总是引用值，否则会得到意想不到的结果!

你不需要引用数字，但如果你这样做总是一个好习惯，因为它是一致的。

问题是，假设我们有一个名为users的表，其中有一个名为current_balance的FLOAT类型的列，如果您运行以下查询:

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

current_balance字段将更新为231608，因为MySQL做了四舍五入，类似地，如果你尝试这个查询:

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

current_balance字段将被更新为231609

相关内容

最新更新

热门标签：