why integer data gets inserted into 'varchar' column without giving the single quotes?

Question

create table student(id int, name varchar(20));

student table created here.*

insert into student values(20,100);

In above query I am inserting the integer value 100 in name column, which is varchar. Why its not giving an error or how its getting inserted?

edit 1: But while inserting a string it is necessary to give single quotes? Ex. insert into student values(20,suraj); gives error

Answer 1

This is handled differently with different databases. For MySQL, an integer is implicitly converted to a varchar for you, but in SQL, it produces an error. As for MySQL, the auto conversion is just part of the loose types some languages have for convenience.

If you want to force MySQL to do type checking, you can use STRICT_ALL_TABLES in your config file. You can also set it in your current session via:

$ SET sql_mode = 'STRICT_ALL_TABLES';

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_strict_all_tables

Hope this helps!

edit 1: With string literals, languages typically require quotes so that the program knows it is a string and not a keyword. For example, if your string was "select", MySQL would need to know you meant that as a string.