I accidentally tried to insert 'asdf' into an integer column. It worked (put 0 in the column), and gave this warning:
Incorrect integer value: 'asdf' for column 'Author' at row 1
How can I cause similar insertions to fail instead of merely issuing a warning?
As of MySQL 5.0, there is an option sql_mode you can set, either in the my.cnf or during startup (--sql-mode='<option>') or during a running session. When you set it to
sql_mode=STRICT_ALL_TABLES
mysql will not issue a warning and insert the wrong value anyways, but it will refuse your statement with an error.
Example:
mysql> create table asdf (id int);
Query OK, 0 rows affected (0.13 sec)
mysql> insert into asdf value ('Hello');
Query OK, 1 row affected, 1 warning (0.38 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'Hello' for column 'id' at row 1 |
+---------+------+-----------------------------------------------------------+
1 row in set (0.02 sec)
mysql> select * from asdf;
+------+
| id |
+------+
| 0 |
+------+
1 row in set (0.03 sec)
mysql> set SESSION sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into asdf value ('Hello');
ERROR 1366 (HY000): Incorrect integer value: 'Hello' for column 'id' at row 1
mysql>
Simply throw an error by using raiseerror(). It is used like an exception handling's "throw" keyword which raises an error on certain pattern
SELECT
case WHEN colname<>IsString(@columnName)
THEN raiseerror()
ELSE expression2
END AS size
FROM `mytab`
Now create a user defined function IsString() in my case which will return boolean value for checking string. If input value is string it will raise an error.
