Disallow inserting text values into integer columns

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 'A开发者_运维技巧uthor' 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.

See also in the manual

---

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.

Check this thread to raise error