Skip to content

nullable json column can not update to null value #136

@majian-poper

Description

@majian-poper

php version and amphp/mysql version

PHP 8.3.14 (cli) (built: Nov 19 2024 15:14:23) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.3.14, Copyright (c) Zend Technologies
    with Zend OPcache v8.3.14, Copyright (c), by Zend Technologies
    with Xdebug v3.4.0, Copyright (c) 2002-2024, by Derick Rethans

"amphp/mysql": "^3.0",

This is my code

$mysqlConfig = new MysqlConfig(
    host: '127.0.0.1',
    port: '3306',
    user: 'root',
    password: 'pass',
    database: 'db'
);

$db = connect($mysqlConfig);

$db->execute("drop table if exists `test`");
$db->execute("create table `test` (`id` bigint unsigned not null auto_increment primary key, `something` json null)");

$db->execute('insert into `test` (`something`) values (?), (?)', [
    json_encode(['a' => 1]),
    null,
]);

dump('before update');
dump(\iterator_to_array($db->query('select * from `test`')));

$db->execute('update `test` set `something` = ? where `id` = ?', [null, 1]); // <--- update sql query

dump('after update');
dump(\iterator_to_array($db->query('select * from `test`')));

output

"before update" 
array:2 [
  0 => array:2 [
    "id" => "1"
    "something" => "{"a": 1}"
  ]
  1 => array:2 [
    "id" => "2"
    "something" => null
  ]
] 

"after update" 
array:2 [
  0 => array:2 [
    "id" => "1"
    "something" => "{"a": 1}" // not affected
  ]
  1 => array:2 [
    "id" => "2"
    "something" => null
  ]
] 

also if we insert null json between other values,

$db->execute('insert into `test` (`something`) values (?), (?), (?)', [
    json_encode(['a' => 1]),
    null,
    json_encode(['c' => 1]),
]);

a Amp\Sql\SqlQueryError will be throw

 MySQL error (3140): #22032 Invalid JSON text: "The document is empty." at position 0 in value for column 'test.something'.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions