Home>

I felt uncomfortable when operating mariaDB.
Isn't it an error to put a character string in a column with int specified at the table definition stage?
Also, if 11 characters or more are entered with varchar (10) etc., this will not be an error and will be truncated at 10 characters.

MariaDB [db]>create table test (id int (11) primary key);
Query OK, 0 rows affected (0.04 sec)
MariaDB [employee_db]>show columns from test;
+ ------- + --------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key |
+ ------- + --------- + ------ + ----- + --------- + ------- +
id | int (11) | YES | | NULL |
+ ------- + --------- + ------ + ----- + --------- + ------- +
1 row in set (0.03 sec)
MariaDB [db]>insert into test (id) values ​​("Ah Ah Ah Ah");
Query OK, 1 row affected, 1 warning (0.01 sec)
MariaDB [db]>select * from test \ G
*************************** 1. row ******************** *******
id: 0
1 row in set (0.00 sec)

Is there an error in this case?
Please give me an answer if you understand.

  • Answer # 1

      

    Is there an error in this case?

    MariaDB (and MySQL) has the concept ofSQL_MODE(MariaDB official).

    IfSQL_MODEisSTRICT_TRANS_TABLESorSTRICT_ALL_TABLES...INSERTwith an inappropriate value fails Rolled back.

    If

    SQL_MODEis set differently ... Inappropriate values ​​may be converted by MariaDB andINSERTas appropriate values.

  • Answer # 2

    MariaDB and its original MySQL also have a place where the check of data entering the table is relaxed and ignored without causing an error to prevent performance degradation.
    To that extent, MariaDB covers with the ability of the program writer. In severe demanding systems such as banks, Oracle is often used to check properly.