MySQL Silent Truncation

I have been burned twice in the past month by default MySQL behaviours. Before we proceed with these gotchas, let's setup a sample database.

Create a sample test database.
$ mysqladmin -u root -p create test
$ mysql -u root -p test

Data truncation or garbage in NOT necessary garbage out. The price of 12,000 was truncated into 12.00
mysql> create table items(price numeric(12, 2));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into items values('12,000');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from items \G;
************************* 1. row *************************
price: 12.00
1 row in set (0.00 sec)

No comments:

Post a Comment