NULL values in MySQL database after I set default '0' to the column

Question

I have the payouts table to whom I set the default as 0.000 for my total_tips column (extract from my table schema):

total_tips       | decimal(12, 4) | YES  |      | 0.0000 | <- Default set as '0.0000'

But now, can anyone explain why I have still NULL values inside my table:

mysql> select total_tips from payouts where id = 4157;
+------------+
| total_tips |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)

Before that I ran a ALTER command which look like this (to set the default value):

mysql> ALTER TABLE payouts change total_tips total_tips decimal(12,4) default 0 ;

Maxime Lorant · Accepted Answer · 2013-07-31 15:40:05Z

3

You maybe didn't set the value as NOT NULL, so the NULL value is accepted. You need to update your whole base with:

UPDATE payouts SET total_tips = 0 WHERE total_tips IS NULL

answered Jul 31, 2013 at 15:40

Maxime Lorant

36.4k19 gold badges91 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ratatouille Over a year ago

I thought that could be the reason but just wanted to confirm

zwaem · Accepted Answer · 2013-07-31 15:44:33Z

3

if you set defaults on existing columns in tables which already stores data then the existing rows are not automatically updated.

i mean this is only true for mssql and mysql. in oracle existing lines WILL BE updated.

answered Jul 31, 2013 at 15:44

zwaem

576 bronze badges

Collectives™ on Stack Overflow

NULL values in MySQL database after I set default '0' to the column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related