MySql array type and default value

Question

I need add field with default value like that

`setting_notification` = 'a:2:{s:19:"other_notifications";i:1;s:21:"message_notifications";i:0;}' 

ALTER TABLE app_users ADD setting_notification tinytext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:array)'

How can do this ?

I try this

ALTER TABLE app_users 
ADD setting_notification LONGTEXT CHARACTER SET utf8 
DEFAULT 'a:2:{s:19:"other_notifications";i:1;s:21:"message_notifications";i:0;}'  
COMMENT '(DC2Type:array)'

And have error

 [Err] 1101 - BLOB, TEXT, GEOMETRY or JSON column 'setting_notification' can't have a default value

and try like that

ALTER TABLE app_users 
ADD setting_notification tinytext 
DEFAULT "a:2:{s:19:\"other_notifications\";i:1;s:21:\"message_notifications\";i:0;}" 
COLLATE utf8_unicode_ci 
NOT NULL COMMENT '(DC2Type:array)'

still have error

[Err] 1101 - BLOB, TEXT, GEOMETRY or JSON column 'setting_notification' can't have a default value

sql version

mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.7.16-0ubuntu0.16.04.1 |
+-------------------------+
1 row in set (0,01 sec)

mysql> SELECT VERSION(); +-------------------------+ | VERSION() | +-------------------------+ | 5.7.16-0ubuntu0.16.04.1 | +-------------------------+ 1 row in set (0,01 sec) — shuba.ivan
– shuba.ivan, Commented Jan 10, 2017 at 14:10

Seb · Accepted Answer · 2017-01-10 14:08:04Z

4

You can add a default value. Why do you use tinytext and not varchar?

ALTER TABLE app_users ADD setting_notification varchar(255) DEFAULT "a:2:{s:19:\"other_notifications\";i:1;s:21:\"message_notifications\";i:0;}" COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:array)'

edited Jan 10, 2017 at 14:08

answered Jan 10, 2017 at 13:50

Seb

1,5511 gold badge12 silver badges19 bronze badges

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

4 Comments

shuba.ivan Over a year ago

[Err] 1101 - BLOB, TEXT, GEOMETRY or JSON column 'setting_notification' can't have a default value

Seb Over a year ago

Is there any reason to use tinytext as a data type? Use varchar and you should be fine.

shuba.ivan Over a year ago

ALTER TABLE app_users  ADD setting_notification VARCHAR  DEFAULT "a:2:{s:19:\"other_notifications\";i:1;s:21:\"message_notifications\";i:0;}"  COLLATE utf8_unicode_ci  NOT NULL COMMENT '(DC2Type:array)'

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT "a:2:{s:19:\"other_notifications\";i:1;s:21:\"message_notifications\";i:' at line 15

Seb Over a year ago

I added a length to the varchar data type. But I do not know how long it should be. Please adjust this.

Collectives™ on Stack Overflow

MySql array type and default value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related