Alex Rivera | Logout

In MySQL, should I quote numbers or not?

Asked 2011-07-21T19:58:40.333
59

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?

mysql sql ansi-sql
Edit
Report

1 Answer

3

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)

answered 2011-07-21T20:05:01.540

Your Answer