bitrix-database

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Direct Database Work

直接数据库操作

Baseline: main 23.0+. ORM is the first choice (

bitrix-orm

— prefer

query()

ConditionTree

, and ORM write APIs including batch / merge /

deleteByFilter

before dropping to SQL). Direct SQL is needed for:

Migrations/DDL in
```
install/index.php
```
/
```
updater.php
```
,
Bulk operations (
```
UPSERT
```
,
```
REPLACE
```
, windows/CTE),
Reports with
```
GROUP BY
```
/aggregates that are cumbersome to build via ORM,
Working with multiple connections (analytical replica, Redis).

基准版本：main 23.0+。ORM是首选方案（

bitrix-orm

— 在使用SQL之前，优先使用

query()

ConditionTree

，以及ORM写入API，包括批量操作/合并/

deleteByFilter

）。在以下场景中需要使用直接SQL：

```
install/index.php
```
/
```
updater.php
```
中的迁移/DDL操作，
批量操作（
```
UPSERT
```
、
```
REPLACE
```
、窗口函数/CTE），
包含
```
GROUP BY
```
/聚合函数的报表，这类报表通过ORM构建较为繁琐，
多连接操作（分析副本、Redis）。

Connection

数据库连接

php

use Bitrix\Main\Application;
use Bitrix\Main\DB\Connection;

/** @var Connection $db */
$db = Application::getConnection();           // default
$db = Application::getConnection('default');
$analytics = Application::getConnection('analytics'); // additional

php

use Bitrix\Main\Application;
use Bitrix\Main\DB\Connection;

/** @var Connection $db */
$db = Application::getConnection();           // 默认连接
$db = Application::getConnection('default');
$analytics = Application::getConnection('analytics'); // 额外连接

Configuration in

.settings.php

.settings.php中的配置

php

'connections' => [
    'value' => [
        'default' => [
            'className' => \Bitrix\Main\DB\MysqliConnection::class,
            'host'      => 'db',
            'database'  => 'bx',
            'login'     => 'bx',
            'password'  => '***',
            'options'   => \Bitrix\Main\DB\Connection::DEFERRED, // 2 — connect on first query
        ],
        'analytics' => [
            'className' => \Bitrix\Main\DB\PgsqlConnection::class,
            'host'      => 'pg',
            'database'  => 'analytics',
            'login'     => 'ro',
            'password'  => '***',
            'options'   => \Bitrix\Main\DB\Connection::DEFERRED,
        ],
        'redis' => [
            'className' => \Bitrix\Main\Data\RedisConnection::class,
            'host'      => 'redis',
            'port'      => 6379,
            'persistent'=> true,
            'serializer'=> \Redis::SERIALIZER_IGBINARY,
            'compression' => \Redis::COMPRESSION_LZ4,
        ],
    ],
    'readonly' => true,
],

options

Connection::PERSISTENT = 1

Connection::DEFERRED = 2

, combined via bitwise OR (

Classes:

```
\Bitrix\Main\DB\MysqliConnection
```
— MySQL (
```
mysqli
```
).
```
\Bitrix\Main\DB\PgsqlConnection
```
— PostgreSQL.

\Bitrix\Main\DB\MssqlConnection

\Bitrix\Main\DB\OracleConnection

— rare.

\Bitrix\Main\Data\MemcacheConnection

MemcachedConnection

RedisConnection

```
\Bitrix\Main\Data\HsphpReadConnection
```
— HandlerSocket (read-only, for high-load
```
SELECT
```
by primary key bypassing SQL).

php

'connections' => [
    'value' => [
        'default' => [
            'className' => \Bitrix\Main\DB\MysqliConnection::class,
            'host'      => 'db',
            'database'  => 'bx',
            'login'     => 'bx',
            'password'  => '***',
            'options'   => \Bitrix\Main\DB\Connection::DEFERRED, // 2 — 首次查询时建立连接
        ],
        'analytics' => [
            'className' => \Bitrix\Main\DB\PgsqlConnection::class,
            'host'      => 'pg',
            'database'  => 'analytics',
            'login'     => 'ro',
            'password'  => '***',
            'options'   => \Bitrix\Main\DB\Connection::DEFERRED,
        ],
        'redis' => [
            'className' => \Bitrix\Main\Data\RedisConnection::class,
            'host'      => 'redis',
            'port'      => 6379,
            'persistent'=> true,
            'serializer'=> \Redis::SERIALIZER_IGBINARY,
            'compression' => \Redis::COMPRESSION_LZ4,
        ],
    ],
    'readonly' => true,
],

options

参数：

Connection::PERSISTENT = 1

（持久连接），

Connection::DEFERRED = 2

（延迟连接），可通过按位或组合（如

）。

SELECT

查询操作（SELECT）

php

$rs = $db->query('SELECT ID, NAME FROM b_user WHERE ACTIVE = "Y"');
$rs = $db->query('SELECT ID FROM b_user', 10);     // LIMIT 10
$rs = $db->query('SELECT ID FROM b_user', 0, 100); // LIMIT 0, 100

while ($row = $rs->fetch())
{
    $id = (int)$row['ID'];
}

foreach ($rs as $row) { /* ... */ }

$id = $db->queryScalar('SELECT COUNT(*) FROM b_user WHERE ACTIVE = "Y"');

```
fetch()
```
— values are processed through field converters (date →
```
Bitrix\Main\Type\DateTime
```
).
```
fetchRaw()
```
— as received from the driver.

$result->getSelectedRowsCount()

$result->getFields()

$result->getResource()

(low-level

mysqli_result

Important:

Result

cannot be "rewound" — if a second pass is needed, materialize it into an array.

php

$rs = $db->query('SELECT ID, NAME FROM b_user WHERE ACTIVE = "Y"');
$rs = $db->query('SELECT ID FROM b_user', 10);     // 限制返回10条
$rs = $db->query('SELECT ID FROM b_user', 0, 100); // 从第0条开始，返回100条

while ($row = $rs->fetch())
{
    $id = (int)$row['ID'];
}

foreach ($rs as $row) { /* ... */ }

$id = $db->queryScalar('SELECT COUNT(*) FROM b_user WHERE ACTIVE = "Y"');

```
fetch()
```
— 返回值会经过字段转换器处理（日期转换为
```
Bitrix\Main\Type\DateTime
```
）。
```
fetchRaw()
```
— 直接返回驱动原始数据。

$result->getSelectedRowsCount()

（获取查询行数）、

$result->getFields()

（获取字段信息）、

$result->getResource()

（底层

mysqli_result

资源）。

注意：

Result

对象无法"回滚"遍历 — 若需二次遍历，需先将其转换为数组。

Custom Converters

自定义转换器

php

$rs = $db->query('SELECT ID, ACTIVE, DATE_REGISTER FROM b_user');
$rs->setConverters(['DATE_REGISTER' => static fn ($v) => $v ? strtotime($v) : null]);
$rs->addFetchDataModifier(static function (array $row): array {
    $row['ACTIVE_BOOL'] = $row['ACTIVE'] === 'Y';
    return $row;
});

php

$rs = $db->query('SELECT ID, ACTIVE, DATE_REGISTER FROM b_user');
$rs->setConverters(['DATE_REGISTER' => static fn ($v) => $v ? strtotime($v) : null]);
$rs->addFetchDataModifier(static function (array $row): array {
    $row['ACTIVE_BOOL'] = $row['ACTIVE'] === 'Y';
    return $row;
});

INSERT/UPDATE/DELETE

增删改操作（INSERT/UPDATE/DELETE）

php

$id = $db->add('my_table', [
    'NAME'    => 'example',
    'CONTENT' => $raw,              // automatically escaped
]);

$lastId = $db->addMulti('my_table', [
    ['NAME' => 'a', 'CONTENT' => '1'],
    ['NAME' => 'b', 'CONTENT' => '2'],
]);

$db->queryExecute(
    'UPDATE my_table SET NAME = "' . $db->getSqlHelper()->forSql($name) . '" WHERE ID = ' . (int)$id
);

add

addMulti

silently discard keys with non-existent columns and escape values themselves. Convenient for fixtures and migrations.

IMPORTANT: the
$binds
parameter in
query/queryScalar/queryExecute
does not create prepared statements — these are only placeholders for LOBs in some drivers. Protect against SQL injections via
SqlExpression
or
SqlHelper
.

php

$id = $db->add('my_table', [
    'NAME'    => 'example',
    'CONTENT' => $raw,              // 自动转义
]);

$lastId = $db->addMulti('my_table', [
    ['NAME' => 'a', 'CONTENT' => '1'],
    ['NAME' => 'b', 'CONTENT' => '2'],
]);

$db->queryExecute(
    'UPDATE my_table SET NAME = "' . $db->getSqlHelper()->forSql($name) . '" WHERE ID = ' . (int)$id
);

add

addMulti

会自动丢弃不存在的列对应的键，并自行转义值。适用于测试数据填充和迁移场景。

重要提示：
query/queryScalar/queryExecute
中的
$binds
参数不会创建预编译语句 — 仅部分驱动中用于LOB类型的占位符。需通过
SqlExpression
或
SqlHelper
防止SQL注入。

SqlHelper — Escaping and Utilities

SqlHelper — 转义与工具方法

php

$h = $db->getSqlHelper();

$h->quote('table.id');            // `table`.`id`
$h->forSql($userInput);           // escapes quotes
$h->convertToDb($value);          // 'v' | 'NULL' | '123'
$h->convertToDbString(null);      // ''
$h->convertToDbString('long', 5); // 'long '  (truncated)
$h->convertToDbInteger('x');      // 0
$h->convertToDbInteger(1e10, 4);  // 2147483647 — 4 byte limit
$h->convertToDbFloat(1.2345, 1);  // '1.2'
$h->convertToDbDate(new \Bitrix\Main\Type\Date('01.01.2025'));      // '2025-01-01'
$h->convertToDbDateTime(new \Bitrix\Main\Type\DateTime());

$h->getCurrentDateTimeFunction();                  // NOW()
$h->addSecondsToDateTime(60, $h->quote('c'));      // DATE_ADD(`c`, INTERVAL 60 SECOND)
$h->addDaysToDateTime(30);                         // DATE_ADD(NOW(), INTERVAL 30 DAY)
$h->getConcatFunction($h->quote('a'), "'-'", $h->quote('b'));
$h->getIsNullFunction($h->quote('a'), 0);          // IFNULL(`a`, 0)
$h->getMatchFunction($h->quote('body'), $h->convertToDb('bitrix')); // MATCH ... AGAINST

SQL function arguments are not automatically escaped — pass them through

quote

convertToDb

yourself.

php

$h = $db->getSqlHelper();

$h->quote('table.id');            // `table`.`id`
$h->forSql($userInput);           // 转义引号
$h->convertToDb($value);          // 转换为数据库兼容格式：'v' | 'NULL' | '123'
$h->convertToDbString(null);      // ''
$h->convertToDbString('long', 5); // 'long ' （截断处理）
$h->convertToDbInteger('x');      // 0
$h->convertToDbInteger(1e10, 4);  // 2147483647 — 4字节整数上限
$h->convertToDbFloat(1.2345, 1);  // '1.2'
$h->convertToDbDate(new \Bitrix\Main\Type\Date('01.01.2025'));      // '2025-01-01'
$h->convertToDbDateTime(new \Bitrix\Main\Type\DateTime());

$h->getCurrentDateTimeFunction();                  // NOW()
$h->addSecondsToDateTime(60, $h->quote('c'));      // DATE_ADD(`c`, INTERVAL 60 SECOND)
$h->addDaysToDateTime(30);                         // DATE_ADD(NOW(), INTERVAL 30 DAY)
$h->getConcatFunction($h->quote('a'), "'-'", $h->quote('b'));
$h->getIsNullFunction($h->quote('a'), 0);          // IFNULL(`a`, 0)
$h->getMatchFunction($h->quote('body'), $h->convertToDb('bitrix')); // MATCH ... AGAINST

SQL函数的参数不会自动转义 — 需自行通过

quote

convertToDb

处理。

UPSERT (

prepareMerge*

)

UPSERT操作（

prepareMerge*

）

php

[$sql] = $h->prepareMerge(
    'b_user_counter',
    ['USER_ID', 'SITE_ID', 'CODE'],
    insertFields: ['USER_ID' => 1, 'SITE_ID' => 's1', 'CODE' => 'visits', 'CNT' => 1],
    updateFields: ['CNT' => new \Bitrix\Main\DB\SqlExpression('?# + ?i', 'CNT', 1)],
);
$db->queryExecute($sql);

There are also

prepareMergeValues

(multiple rows at once),

prepareMergeSelect

(from subquery),

prepareMergeMultiple

(

REPLACE INTO

, splits batches for large bulks).

php

[$sql] = $h->prepareMerge(
    'b_user_counter',
    ['USER_ID', 'SITE_ID', 'CODE'],
    insertFields: ['USER_ID' => 1, 'SITE_ID' => 's1', 'CODE' => 'visits', 'CNT' => 1],
    updateFields: ['CNT' => new \Bitrix\Main\DB\SqlExpression('?# + ?i', 'CNT', 1)],
);
$db->queryExecute($sql);

此外还有

prepareMergeValues

（批量处理多行）、

prepareMergeSelect

（从子查询获取数据）、

prepareMergeMultiple

（

REPLACE INTO

，针对大量数据拆分批次）。

SqlExpression — Parameterized Queries

SqlExpression — 参数化查询

php

use Bitrix\Main\DB\SqlExpression;

$sql = new SqlExpression(
    'SELECT * FROM ?# WHERE (ID = ?i OR ID > ?f) AND NAME = ?s AND CREATED > ?',
    'b_user',
    1,
    1.23,
    'admin',
    new \Bitrix\Main\Type\Date('01.01.2025'),
);

$db->query($sql);
echo (string)$sql; // compiled SQL

Placeholders:

```
?
```
— auto: strings, numbers,
```
Date/DateTime
```
,
```
null
```
→
```
NULL
```
.
```
?s
```
— string.
```
?i
```
— integer.
```
?f
```
— float.
```
?#
```
— identifier (table/column name, wrapped in quotes).
```
?v
```
—
```
VALUES(...)
```
for INSERT/UPDATE.

For dates in

Date

DateTime

use

— you'll get

'2025-01-01 00:00:00'

;

?s

will give string representation in site format.

php

use Bitrix\Main\DB\SqlExpression;

$sql = new SqlExpression(
    'SELECT * FROM ?# WHERE (ID = ?i OR ID > ?f) AND NAME = ?s AND CREATED > ?',
    'b_user',
    1,
    1.23,
    'admin',
    new \Bitrix\Main\Type\Date('01.01.2025'),
);

$db->query($sql);
echo (string)$sql; // 编译后的SQL语句

占位符说明：

```
?
```
— 自动识别类型：字符串、数字、
```
Date/DateTime
```
、
```
null
```
→
```
NULL
```
。
```
?s
```
— 字符串类型。
```
?i
```
— 整数类型。
```
?f
```
— 浮点数类型。
```
?#
```
— 标识符（表/列名，自动添加引号）。
```
?v
```
— INSERT/UPDATE中的
```
VALUES(...)
```
。

对于

Date

DateTime

类型的日期，使用

占位符会得到

'2025-01-01 00:00:00'

；使用

?s

则会返回站点格式的字符串。

Transactions

事务操作

php

$db = Application::getConnection();
$db->startTransaction();
try {
    $db->queryExecute('...');
    $db->commitTransaction();
} catch (\Throwable $e) {
    $db->rollbackTransaction();
    throw $e;
}

Keep transactions short. ORM operations inside a transaction are supported — use the same connection.

php

$db = Application::getConnection();
$db->startTransaction();
try {
    $db->queryExecute('...');
    $db->commitTransaction();
} catch (\Throwable $e) {
    $db->rollbackTransaction();
    throw $e;
}

事务应保持简短。事务内支持ORM操作 — 需使用同一数据库连接。

SqlTracker

Enable SQL query logging for debugging. Call

startTracker()

, then

startFileLog($path)

to dump queries to a file in development:

php

$tracker = \Bitrix\Main\Application::getConnection()->startTracker();
$tracker->startFileLog($_SERVER['DOCUMENT_ROOT'] . '/mysql_debug.sql');
// ... queries ...
$queries = $tracker->getQueries();
$tracker->stop();

Use only in development (same pattern as kernel

$DBDebugToFile

start.php

启用SQL查询日志用于调试。调用

startTracker()

，再调用

startFileLog($path)

可在开发环境中将查询日志写入文件：

php

$tracker = \Bitrix\Main\Application::getConnection()->startTracker();
$tracker->startFileLog($_SERVER['DOCUMENT_ROOT'] . '/mysql_debug.sql');
// ... 执行查询 ...
$queries = $tracker->getQueries();
$tracker->stop();

仅在开发环境中使用（与

start.php

中的内核

$DBDebugToFile

模式一致）。

PostgreSQL

PostgreSQL支持

PgsqlConnection

is supported (Enterprise for PostgreSQL license). Not all kernel/marketplace modules support PostgreSQL — verify before migration. See skill

bitrix-postgresql

PgsqlConnection

已支持（需PostgreSQL企业版授权）。并非所有内核/市场模块都支持PostgreSQL — 迁移前请确认兼容性。详见技能

bitrix-postgresql

。

after_connect_d7.php

Place post-connect hooks in

/local/php_interface/after_connect_d7.php

(charset,

sql_mode

, DB timezone). Included by

ConnectionPool

after a successful connect — see skill

bitrix-project-structure

可在

/local/php_interface/after_connect_d7.php

中添加连接后钩子（如字符集、

sql_mode

、数据库时区设置）。

ConnectionPool

在连接成功后会加载该文件 — 详见技能

bitrix-project-structure

。

bitrix-database

Original

Translation

Direct Database Work

直接数据库操作

Connection

数据库连接

Configuration in
`.settings.php`

.settings.php中的配置

SELECT

查询操作（SELECT）

Custom Converters

自定义转换器

INSERT/UPDATE/DELETE

增删改操作（INSERT/UPDATE/DELETE）

SqlHelper — Escaping and Utilities

SqlHelper — 转义与工具方法

UPSERT (
`prepareMerge*`
)

UPSERT操作（
`prepareMerge*`
）

SqlExpression — Parameterized Queries

SqlExpression — 参数化查询

Transactions

事务操作

SqlTracker

SqlTracker

PostgreSQL

PostgreSQL支持

after_connect_d7.php

after_connect_d7.php

bitrix-database

Original

Translation

Direct Database Work

直接数据库操作

Connection

数据库连接

Configuration in .settings.php

.settings.php中的配置

SELECT

查询操作（SELECT）

Custom Converters

自定义转换器

INSERT/UPDATE/DELETE

增删改操作（INSERT/UPDATE/DELETE）

SqlHelper — Escaping and Utilities

SqlHelper — 转义与工具方法

UPSERT (prepareMerge*)

UPSERT操作（prepareMerge*）

SqlExpression — Parameterized Queries

SqlExpression — 参数化查询

Transactions

事务操作

SqlTracker

SqlTracker

PostgreSQL

PostgreSQL支持

after_connect_d7.php

after_connect_d7.php

Configuration in
`.settings.php`

UPSERT (
`prepareMerge*`
)

UPSERT操作（
`prepareMerge*`
）