mysql can datatypes in VALUES clause be different type then the column data type?

Question

Hi all(Note that I have similar topics with different questions),

I'm learning MySQL. I came across some multiple-choice questions. If I doubted what the answer was or was not convinced it's correct I started searching google, stackoverflow and the mysql site. However for some I still couldn't confirm what the correct answer was.

Question: True or false? "The number and datatypes of values specified in the VALUES clause of an INSERT statement must match the number and datatypes of corresponding specified in the INTO clause".

I think this is an interesting question as if a column is specified VARCHAR I can just use an int to insert into it. From what I think MySQL casts what you specified if possible. So this would be false. As an example:

CREATE TABLE a (name VARCHAR(1));
INSERT INTO a (1);
INSERT INTO a (1.1);

The other way around it works as well. Create a INT column. Insert even a string literal and MySQL still allows it.

If you have any remark about this question/answer or for any of my remarks I'm happy to hear it!

Thanks a lot in advance!

Maxim Krizhanovsky Maxim Krizhanovsky · Accepted Answer · 2013-08-18T17:06:14

The correct answer actually depends on the SQL mode, as in STRICT mode you'll receive error if the data type is wrong, and in other modes MySQL will do the best it can (for example will truncate everyting after the decimal point if you are trying to insert float in int column) to execute the query and will just produce warnings about the implicit casts it did.

mysql can datatypes in VALUES clause be different type then the column data type?

1 Answers