MySQL-报价数字或没有? [英] mysql - quote numbers or not?

问题描述

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

所以-在这些示例中，我选择了 string 并在mysql中将其实际存储为INT，然后在INT列中插入了 string

我不太明白为什么它会按这里的方式工作.为什么允许在INT列中插入字符串?

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

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

谢谢！

解决方案

MySQL与PHP非常相似，它将尽最大可能自动转换数据类型.由于您正在使用int字段(左侧)，因此它将尝试将参数的右侧也透明地转换为int，因此'9'变为9.

严格来说，引号是不必要的，并且迫使MySQL执行类型转换/转换，因此浪费了CPU时间.实际上，除非您运行的是Google大小的操作，否则这种转换开销在微观上将很小.

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?

thanks!

解决方案

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.

这篇关于MySQL-报价数字或没有?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！