varchar datatype accepting integer values

Question

below is the test table created. As data type is set to varchar it shouldn’t accept int values.

desc c\G
*************************** 1. row ***************************

  Field: t
   Type: int(6)
   Null: NO
    Key: 
Default: NULL
  Extra: 

*************************** 2. row ***************************

  Field: h
   Type: varchar(5)
   Null: NO
    Key: 
Default: NULL
  Extra: 

*************************** 3. row ***************************

  Field: e
   Type: varchar(6)
   Null: NO
    Key: 
Default: 0
  Extra: 



MariaDB [test1]> insert into c values  (3,098765,654365);
Query OK, 1 row affected (0.00 sec).

My question is how to ask mariadb/mysql to prompt for error or stopped accepting the int values in varchar datatype.I already have strict mode set to

sql_mode = STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES

Answer 1

Your question relates to implicit type conversions.

I searched the MariaDB documentation but their description is not very detailed except to show examples of this happening (see https://mariadb.com/kb/en/mariadb/type-conversion/). MySQL has a similar page which definitively states it implicitly converts to/from strings as required (see http://dev.mysql.com/doc/refman/5.7/en/type-conversion.html).

For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

The ISO-92 standard allows for implicit conversions but doesn't define all of them, it's vendor specific. However you can't stop this happening in SQL Server, or Oracle, I suspect it's by convention and can't be avoided.