In MySQL, should I quote numbers or not?

user_471891 0
Viewed 42934

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?

ansi-sql mysql sql
8 Answers

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!

user_19597620
Related