UPDATE

UPDATE 命令用于将指定表中现有文档的行式属性值更改为新值。请注意，您不能更新全文字段或列式属性的内容。如果有此类需求，请使用 REPLACE。

属性更新支持 RT、PQ 和普通表。只要属性存储在行式存储中，所有属性类型都可以更新。

请注意，文档 ID 不能被更新。

需要注意的是，更新属性会禁用其二级索引。如果维护二级索引的连续性至关重要，请考虑完全或部分替换文档。

关于 UPDATE 与部分 REPLACE 的更多信息，请参见这里。

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • Python-asyncio
  • javascript
  • Java
  • C#
  • Rust
  • TypeScript
  • Go
📋
UPDATE products SET enabled=0 WHERE id=10;
‹›
Response
Query OK, 1 row affected (0.00 sec)

单条语句中可以更新多个属性。示例：

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • Python-asyncio
  • javascript
  • Java
  • C#
  • Rust
  • TypeScript
  • Go
📋
UPDATE products
SET price=100000000000,
    coeff=3465.23,
    tags1=(3,6,4),
    tags2=()
WHERE MATCH('phone') AND enabled=1;
‹›
Response
Query OK, 148 rows affected (0.0 sec)

当给 32 位属性赋予超出范围的值时，它们会被截断为其低 32 位，且不会有提示。例如，如果您尝试用值 4294967297 更新 32 位无符号整数，实际存储的值将是 1，因为 4294967297（十六进制为 0x100000001）的低 32 位是 1（十六进制为 0x00000001）。

UPDATE 可用于对数值数据类型或数值数据类型数组执行部分 JSON 更新。只需确保不要用浮点数值更新整数值，因为浮点数会被四舍五入。

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • Python-asyncio
  • javascript
  • Java
  • C#
  • Rust
  • TypeScript
  • Go
📋
insert into products (id, title, meta) values (1,'title','{"tags":[1,2,3]}');
update products set meta.tags[0]=100 where id=1;
‹›
Response
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

更新其他数据类型或更改 JSON 属性中的属性类型需要进行完整的 JSON 更新。

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • Python-asyncio
  • javascript
  • Java
  • C#
  • Rust
  • TypeScript
  • Go
📋
insert into products values (1,'title','{"tags":[1,2,3]}');
update products set data='{"tags":["one","two","three"]}' where id=1;
‹›
Response
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

When using replication, the table name should be prepended with cluster_name: (in SQL) so that updates will be propagated to all nodes in the cluster. For queries via HTTP, you should set a cluster property. See setting up replication for more information.

{
  "cluster":"nodes4",
  "table":"test",
  "id":1,
  "doc":
  {
    "gid" : 100,
    "price" : 1000
  }
}
‹›
  • SQL
  • JSON
  • PHP
  • Python
  • Python-asyncio
  • javascript
  • Java
  • C#
  • Rust
  • TypeScript
  • Go
📋
update weekly:posts set enabled=0 where id=1;

Updates via SQL

Here is the syntax for the SQL UPDATE statement:

UPDATE table SET col1 = newval1 [, ...] WHERE where_condition [OPTION opt_name = opt_value [, ...]] [FORCE|IGNORE INDEX(id)]

where_condition has the same syntax as in the SELECT statement.

Multi-value attribute value sets must be specified as comma-separated lists in parentheses. To remove all values from a multi-value attribute, just assign () to it.

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • Python-asyncio
  • javascript
  • Java
  • C#
  • Rust
  • TypeScript
  • Go
📋
UPDATE products SET tags1=(3,6,4) WHERE id=1;
UPDATE products SET tags1=() WHERE id=1;
‹›
Response
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

OPTION clause is a Manticore-specific extension that lets you control a number of per-update options. The syntax is:

OPTION <optionname>=<value> [ , ... ]

The options are the same as for the SELECT statement. Specifically for the UPDATE statement, you can use these options:

  • 'ignore_nonexistent_columns' - If set to 1, it indicates that the update will silently ignore any warnings about trying to update a column which does not exist in the current table schema. The default value is 0.
  • 'strict' - This option is used in partial JSON attribute updates. By default (strict=1), UPDATE will result in an error if the UPDATE query tries to perform an update on non-numeric properties. With strict=0, if multiple properties are updated and some are not allowed, the UPDATE will not result in an error and will perform the changes only on allowed properties (with the rest being ignored). If none of the SET changes of the UPDATE re permitted, the command will result in an error even with strict=0.

Query optimizer hints

In rare cases, Manticore's built-in query analyzer may be incorrect in understanding a query and determining whether a table by ID should be used. This can result in poor performance for queries like UPDATE ... WHERE id = 123. For information on how to force the optimizer to use a docid index, see Query optimizer hints.

Updates via HTTP JSON

Updates using HTTP JSON protocol are performed via the /update endpoint. The syntax is similar to the /insert endpoint, but this time the doc property is mandatory.

The server will respond with a JSON object stating if the operation was successful or not.

‹›
  • JSON
JSON
📋
⚙
POST /update
{
  "table":"test",
  "id":1,
  "doc":
   {
     "gid" : 100,
     "price" : 1000
    }
}
‹›
Response
{
  "table": "test",
  "_id": 1,
  "result": "updated"
}

The ID of the document that needs to be updated can be set directly using the id property, as shown in the previous example, or you can update documents by query and apply the update to all the documents that match the query:

‹›
  • JSON
JSON
📋
⚙
POST /update
{
  "table":"test",
  "doc":
  {
    "price" : 1000
  },
  "query":
  {
    "match": { "*": "apple" }
  }
}
‹›
Response
{
  "table":"products",
  "updated":1
}

查询语法与/search endpoint中的相同。请注意，不能同时指定id和query。

刷新属性

FLUSH ATTRIBUTES

FLUSH ATTRIBUTES命令确保所有活动表中内存中的属性更新都被刷新到磁盘。它返回一个标记，标识结果的磁盘状态，该标记表示自服务器启动以来实际执行的磁盘属性保存次数。

mysql> UPDATE testindex SET channel_id=1107025 WHERE id=1;
Query OK, 1 row affected (0.04 sec)
mysql> FLUSH ATTRIBUTES;
+------+
| tag  |
+------+
|    1 |
+------+
1 row in set (0.19 sec)

另请参见attr_flush_period设置。

批量更新

您可以使用/bulk端点在一次调用中执行多个更新操作。此端点仅适用于Content-Type设置为application/x-ndjson的数据。数据应格式化为换行分隔的JSON（NDJSON）。本质上，这意味着每行应包含一个JSON语句，并以换行符\n结束，可能还有\r。

‹›
  • JSON
JSON
📋
⚙
POST /bulk
{ "update" : { "table" : "products", "id" : 1, "doc": { "price" : 10 } } }
{ "update" : { "table" : "products", "id" : 2, "doc": { "price" : 20 } } }
‹›
Response
{
   "items":
   [
      {
         "update":
         {
            "table":"products",
            "_id":1,
            "result":"updated"
         }
      },
      {
         "update":
         {
            "table":"products",
            "_id":2,
            "result":"updated"
         }
      }
   ],
   "errors":false
}

/bulk端点支持插入、替换和删除。每个语句以操作类型开始（此处为update）。以下是支持的操作列表：

  • insert：插入文档。语法与/insert endpoint相同。
  • create：insert的同义词
  • replace：替换文档。语法与/replace相同。
  • index：replace的同义词
  • update：更新文档。语法与/update相同。
  • delete：删除文档。语法与/delete endpoint相同。

也支持通过查询进行更新和删除。

‹›
  • JSON
  • PHP
  • Python
  • Python-asyncio
  • javascript
  • Java
  • C#
  • Rust
  • TypeScript
  • Go
📋
⚙
POST /bulk
{ "update" : { "table" : "products", "doc": { "coeff" : 1000 }, "query": { "range": { "price": { "gte": 1000 } } } } }
{ "update" : { "table" : "products", "doc": { "coeff" : 0 }, "query": { "range": { "price": { "lt": 1000 } } } } }
‹›
Response
{
  "items":
  [
    {
      "update":
      {
        "table":"products",
        "updated":1
      }
    },
    {
      "update":
      {
        "table":"products",
        "updated":3
      }
    }
  ],
  "errors":false
}

请注意，批量操作将在遇到第一个错误的查询时停止。

与更新相关的设置

attr_update_reserve

attr_update_reserve=size

attr_update_reserve是每个表的设置，用于确定为blob属性更新保留的空间。此设置是可选的，默认值为128k。

当blob属性（MVAs、字符串、JSON）被更新时，其长度可能会变化。如果更新后的字符串（或MVA，或JSON）比旧的短，则会覆盖.spb文件中的旧内容。然而，如果更新后的字符串更长，更新将写入.spb文件的末尾。该文件是内存映射的，这意味着根据操作系统对内存映射文件的实现，调整其大小可能是一个相当缓慢的过程。

为了避免频繁调整大小，您可以使用此选项指定在.spb文件末尾保留的额外空间。

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • Python-asyncio
  • javascript
  • Java
  • C#
  • Rust
  • TypeScript
  • Go
  • CONFIG
📋
create table products(title text, price float) attr_update_reserve = '1M'

attr_flush_period

attr_flush_period = 900 # persist updates to disk every 15 minutes

更新属性时，变更首先写入属性的内存副本。此设置允许设置刷新更新到磁盘的间隔。默认值为0，表示禁用周期性刷新，但在正常关闭时仍会进行刷新。

REPLACE 删除文档
Last modified: August 28, 2025