1

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
2
  • The strict mode "turns warnings to errors" - there is no warning for the implicit int->varchar conversion so there can be no error. MySQL statements just aren't strongly enough typed to check for that.
    – jkavalik
    Commented May 11, 2016 at 7:32
  • Not a bug. Some things should be done in application code, not the database.
    – Rick James
    Commented May 24, 2016 at 22:26

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.