Level 14
BLOB, TEXT, GEOMETRY and JSON column can't have a default value.
If you are using Eloquent model then you can add default values using:
protected $attributes = [
'notification_via' => ['database', 'via']
];
10 likes
Jan 4, 2020
9
Level 13
Default value for json data type in migration
I want to set default value for json data type:
Schema::create('users', function (Blueprint $table) {
$table->bigIncrements('id');
$table->json('notifications_via')->default(['database','via']);
$table->timestamps();
});
In user model i cast notifications_via to array but i'll give error of:
Array to string conversion
Level 1
BLOB, TEXT, GEOMETRY and JSON column can't have a default value.
This has been changed in MySQL 8.0.13
From this version onward its possible to define default value for JSON type.
SQL query that sets default value for JSON:
CREATE TABLE IF NOT EXISTS `test` (
`json` JSON NOT NULL DEFAULT ('{}'),
);
(Notice the () around default value that seems to be mandatory in this case for MySQL). Using $table->json('json')->default('{}') does not ensure that () is appended to query.
Soo because
Schema::table('device', function(Blueprint $table) {
$table->json('config')->default('{}');
});
This migration would produce this error:
Syntax error or access violation: 1101 BLOB, TEXT, GEOMETRY or JSON column 'config' can't have a default value
I guess the only real way to define default value for JSON column with MySQL is to use a raw sql statement:
DB::statement('ALTER TABLE `device` ADD COLUMN config JSON NOT NULL DEFAULT (\'{}\')');
3 likes
Level 67
Untested... wild guess.
Schema::create('users', function (Blueprint $table) {
$table->bigIncrements('id');
$table->json('notifications_via')->default(json_encode(['database','via']));
$table->timestamps();
});
3 likes
Level 14
This will create an error is a limit of mysql:
1 PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1101 BLOB, TEXT, GEOMETRY or JSON column can't have a default value")
Level 8
You can add in your model:
/**
* The attributes that should be cast to native types.
*
* @var array
*/
protected $casts = [
'email_verified_at' => 'datetime',
'settings' => 'array',
];
/**
* The model's default values for attributes.
*
* @var array
*/
protected $attributes = [
'settings' => '{
"theme": "minimalist",
"color": "light"
}'
];
Now your settings column has a default value, important remember any value needs a key.
3 likes
Level 4
When do those values get created in the database? When the migration is run?
Edit: I'm trying to achieve a group of default settings (to be changed by users when needed) in a json type column - for context.
1 like
Level 4
They are created when you insert new value into the database
Level 34
If you are using MySQL >= 8.0.13, you can use this:
$table->json('movies')->default(new Expression('(JSON_ARRAY())'));
See: https://laravel.com/docs/migrations#default-expressions
Although that'd be great if Laravel could use that expression automatically when setting a default value for JSON columns. That might be a good PR.
6 likes
Level 2
@clem This is perfect, thank you!
Please or to participate in this conversation.