mysql-workbench column row with DEFAULT value of 0 not NULL?

Question

I have a database with the column: clicks int(11) DEFAULT NULL.

If i give this column a default value of NULL the increment of:

UPDATE table SET clicks = clicks + 1 WHERE id=:id");

is not working, but when i change it to 0, manually, everything works.

How can i give the column the default value of 0.

By the way if i type 0 as default value in mysql workbench, the database still displays NULL, no matter what.

What am i missing?

munsifali · Accepted Answer · 2018-08-27 08:03:26Z

1

can you try below query.

UPDATE table SET clicks = ifnull(clicks,0) + 1 WHERE id=:id");

Also, you can try altering your table.

  ALTER TABLE table
  CHANGE COLUMN clicks clicks INT DEFAULT 0;

munsifali

1,7432 gold badges24 silver badges44 bronze badges

answered Aug 26, 2018 at 15:56

shashankqv

5254 silver badges14 bronze badges

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

3 Comments

nope it says warning: Syntax error or access violation: 1064

then maybe try to change your table schema using alter query. alter table table change column clicks clicks int default 0;

ok now it works ! I recreated the table with default 0 and it works now, thanks a lot.