如何向SQLite的BLOB列插入或修改附加值？

Got it, let's work through how to handle updating, adding, and removing values in that BLOB status column. Since it's storing JSON as a BLOB, we first need to convert it to a JSON-compatible type, make our changes, then convert it back. Below are examples for the most popular database systems:

MySQL/MariaDB #

MySQL has built-in JSON functions that work well once we cast the BLOB to JSON.

Add a new key-value pair (e.g., color: 'red') #

This uses JSON_SET to insert the new field, then casts back to BLOB:

UPDATE vehicles
SET status = CAST(
  JSON_SET(
    CAST(status AS JSON),
    '$.color', 'red'
  ) AS BLOB
);

Add a WHERE clause (like WHERE id = 1) if you only want to update specific rows instead of the entire table.

Modify an existing value #

To update, say, the condition field to "used":

UPDATE vehicles
SET status = CAST(
  JSON_SET(
    CAST(status AS JSON),
    '$.condition', 'used'
  ) AS BLOB
)
WHERE id = 1; -- Target specific rows here

Delete a key-value pair #

Use JSON_REMOVE to drop a field like condition:

UPDATE vehicles
SET status = CAST(
  JSON_REMOVE(
    CAST(status AS JSON),
    '$.condition'
  ) AS BLOB
)
WHERE id = 1;

PostgreSQL #

PostgreSQL uses bytea for BLOBs, so we'll convert to text first, then to jsonb for modifications.

Add a new key-value pair #

UPDATE vehicles
SET status = CAST(
  jsonb_set(
    CAST(CAST(status AS text) AS jsonb),
    '{color}', '"red"'
  ) AS bytea
);

Modify an existing value #

Update condition to "used":

UPDATE vehicles
SET status = CAST(
  jsonb_set(
    CAST(CAST(status AS text) AS jsonb),
    '{condition}', '"used"'
  ) AS bytea
)
WHERE id = 1;

Delete a key-value pair #

Use jsonb_delete to remove a field:

UPDATE vehicles
SET status = CAST(
  jsonb_delete(
    CAST(CAST(status AS text) AS jsonb),
    '{condition}'
  ) AS bytea
)
WHERE id = 1;

SQL Server #

SQL Server uses varbinary(max) for BLOBs. We'll cast to nvarchar(max) to use JSON functions.

Add a new key-value pair #

UPDATE vehicles
SET status = CAST(
  JSON_MODIFY(
    CAST(status AS nvarchar(max)),
    '$.color', 'red'
  ) AS varbinary(max)
);

Modify an existing value #

Update condition to "used":

UPDATE vehicles
SET status = CAST(
  JSON_MODIFY(
    CAST(status AS nvarchar(max)),
    '$.condition', 'used'
  ) AS varbinary(max)
)
WHERE id = 1;

Delete a key-value pair #

In SQL Server 2017+, you can set the key to NULL with JSON_MODIFY to delete it:

UPDATE vehicles
SET status = CAST(
  JSON_MODIFY(
    CAST(status AS nvarchar(max)),
    '$.condition', NULL
  ) AS varbinary(max)
)
WHERE id = 1;

Important Notes #

• Make sure your database version supports these JSON functions (MySQL 5.7+, PostgreSQL 9.5+, SQL Server 2016+).
• Always test with a SELECT first to verify the conversion: e.g., SELECT CAST(status AS JSON) FROM vehicles WHERE id = 1 (MySQL) to check the original JSON.
• Backup your data before running bulk updates, and use WHERE clauses to avoid modifying unintended rows.

内容的提问来源于stack exchange，提问作者Andriy Kondzolko