1169 lines
34 KiB
PHP
1169 lines
34 KiB
PHP
<?php
|
||
|
||
declare(strict_types=1);
|
||
|
||
/**
|
||
* This file is part of CodeIgniter 4 framework.
|
||
*
|
||
* (c) CodeIgniter Foundation <admin@codeigniter.com>
|
||
*
|
||
* For the full copyright and license information, please view
|
||
* the LICENSE file that was distributed with this source code.
|
||
*/
|
||
|
||
namespace CodeIgniter\Database\MySQLi;
|
||
|
||
use CodeIgniter\Database\BaseConnection;
|
||
use CodeIgniter\Database\Exceptions\DatabaseException;
|
||
use CodeIgniter\Database\TableName;
|
||
use CodeIgniter\Exceptions\LogicException;
|
||
use mysqli;
|
||
use mysqli_result;
|
||
use mysqli_sql_exception;
|
||
use stdClass;
|
||
use Throwable;
|
||
|
||
/**
|
||
* Connection for MySQLi
|
||
*
|
||
* @extends BaseConnection<mysqli, mysqli_result>
|
||
*/
|
||
class Connection extends BaseConnection
|
||
{
|
||
/**
|
||
* Database driver
|
||
*
|
||
* @var string
|
||
*/
|
||
public $DBDriver = 'MySQLi';
|
||
|
||
/**
|
||
* DELETE hack flag
|
||
*
|
||
* Whether to use the MySQL "delete hack" which allows the number
|
||
* of affected rows to be shown. Uses a preg_replace when enabled,
|
||
* adding a bit more processing to all queries.
|
||
*
|
||
* @var bool
|
||
*/
|
||
public $deleteHack = true;
|
||
|
||
/**
|
||
* Identifier escape character
|
||
*
|
||
* @var string
|
||
*/
|
||
public $escapeChar = '`';
|
||
|
||
/**
|
||
* MySQLi object
|
||
*
|
||
* Has to be preserved without being assigned to $connId.
|
||
*
|
||
* @var false|mysqli
|
||
*/
|
||
public $mysqli;
|
||
|
||
/**
|
||
* MySQLi constant
|
||
*
|
||
* For unbuffered queries use `MYSQLI_USE_RESULT`.
|
||
*
|
||
* Default mode for buffered queries uses `MYSQLI_STORE_RESULT`.
|
||
*
|
||
* @var int
|
||
*/
|
||
public $resultMode = MYSQLI_STORE_RESULT;
|
||
|
||
/**
|
||
* Use MYSQLI_OPT_INT_AND_FLOAT_NATIVE
|
||
*
|
||
* @var bool
|
||
*/
|
||
public $numberNative = false;
|
||
|
||
/**
|
||
* Use MYSQLI_CLIENT_FOUND_ROWS
|
||
*
|
||
* Whether affectedRows() should return number of rows found,
|
||
* or number of rows changed, after an UPDATE query.
|
||
*
|
||
* @var bool
|
||
*/
|
||
public $foundRows = false;
|
||
|
||
/**
|
||
* Connect to the database.
|
||
*
|
||
* @return false|mysqli
|
||
*
|
||
* @throws DatabaseException
|
||
*/
|
||
public function connect(bool $persistent = false)
|
||
{
|
||
// Do we have a socket path?
|
||
if ($this->hostname[0] === '/') {
|
||
$hostname = null;
|
||
$port = null;
|
||
$socket = $this->hostname;
|
||
} else {
|
||
$hostname = $persistent ? 'p:' . $this->hostname : $this->hostname;
|
||
$port = empty($this->port) ? null : $this->port;
|
||
$socket = '';
|
||
}
|
||
|
||
$clientFlags = ($this->compress === true) ? MYSQLI_CLIENT_COMPRESS : 0;
|
||
$this->mysqli = mysqli_init();
|
||
|
||
mysqli_report(MYSQLI_REPORT_ALL & ~MYSQLI_REPORT_INDEX);
|
||
|
||
$this->mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 10);
|
||
|
||
if ($this->numberNative === true) {
|
||
$this->mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
|
||
}
|
||
|
||
if (isset($this->strictOn)) {
|
||
if ($this->strictOn) {
|
||
$this->mysqli->options(
|
||
MYSQLI_INIT_COMMAND,
|
||
"SET SESSION sql_mode = CONCAT(@@sql_mode, ',', 'STRICT_ALL_TABLES')"
|
||
);
|
||
} else {
|
||
$this->mysqli->options(
|
||
MYSQLI_INIT_COMMAND,
|
||
"SET SESSION sql_mode = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
|
||
@@sql_mode,
|
||
'STRICT_ALL_TABLES,', ''),
|
||
',STRICT_ALL_TABLES', ''),
|
||
'STRICT_ALL_TABLES', ''),
|
||
'STRICT_TRANS_TABLES,', ''),
|
||
',STRICT_TRANS_TABLES', ''),
|
||
'STRICT_TRANS_TABLES', '')"
|
||
);
|
||
}
|
||
}
|
||
|
||
if (is_array($this->encrypt)) {
|
||
$ssl = [];
|
||
|
||
if (! empty($this->encrypt['ssl_key'])) {
|
||
$ssl['key'] = $this->encrypt['ssl_key'];
|
||
}
|
||
if (! empty($this->encrypt['ssl_cert'])) {
|
||
$ssl['cert'] = $this->encrypt['ssl_cert'];
|
||
}
|
||
if (! empty($this->encrypt['ssl_ca'])) {
|
||
$ssl['ca'] = $this->encrypt['ssl_ca'];
|
||
}
|
||
if (! empty($this->encrypt['ssl_capath'])) {
|
||
$ssl['capath'] = $this->encrypt['ssl_capath'];
|
||
}
|
||
if (! empty($this->encrypt['ssl_cipher'])) {
|
||
$ssl['cipher'] = $this->encrypt['ssl_cipher'];
|
||
}
|
||
|
||
if ($ssl !== []) {
|
||
if (isset($this->encrypt['ssl_verify'])) {
|
||
if ($this->encrypt['ssl_verify']) {
|
||
if (defined('MYSQLI_OPT_SSL_VERIFY_SERVER_CERT')) {
|
||
$this->mysqli->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, 1);
|
||
}
|
||
}
|
||
// Apparently (when it exists), setting MYSQLI_OPT_SSL_VERIFY_SERVER_CERT
|
||
// to FALSE didn't do anything, so PHP 5.6.16 introduced yet another
|
||
// constant ...
|
||
//
|
||
// https://secure.php.net/ChangeLog-5.php#5.6.16
|
||
// https://bugs.php.net/bug.php?id=68344
|
||
elseif (defined('MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT') && version_compare($this->mysqli->client_info, 'mysqlnd 5.6', '>=')) {
|
||
$clientFlags += MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT;
|
||
}
|
||
}
|
||
|
||
$this->mysqli->ssl_set(
|
||
$ssl['key'] ?? null,
|
||
$ssl['cert'] ?? null,
|
||
$ssl['ca'] ?? null,
|
||
$ssl['capath'] ?? null,
|
||
$ssl['cipher'] ?? null
|
||
);
|
||
}
|
||
|
||
$clientFlags += MYSQLI_CLIENT_SSL;
|
||
}
|
||
|
||
if ($this->foundRows) {
|
||
$clientFlags += MYSQLI_CLIENT_FOUND_ROWS;
|
||
}
|
||
|
||
try {
|
||
if ($this->mysqli->real_connect(
|
||
$hostname,
|
||
$this->username,
|
||
$this->password,
|
||
$this->database,
|
||
$port,
|
||
$socket,
|
||
$clientFlags
|
||
)) {
|
||
// Prior to version 5.7.3, MySQL silently downgrades to an unencrypted connection if SSL setup fails
|
||
if (($clientFlags & MYSQLI_CLIENT_SSL) !== 0 && version_compare($this->mysqli->client_info, 'mysqlnd 5.7.3', '<=')
|
||
&& empty($this->mysqli->query("SHOW STATUS LIKE 'ssl_cipher'")->fetch_object()->Value)
|
||
) {
|
||
$this->mysqli->close();
|
||
$message = 'MySQLi was configured for an SSL connection, but got an unencrypted connection instead!';
|
||
log_message('error', $message);
|
||
|
||
if ($this->DBDebug) {
|
||
throw new DatabaseException($message);
|
||
}
|
||
|
||
return false;
|
||
}
|
||
|
||
if (! $this->mysqli->set_charset($this->charset)) {
|
||
log_message('error', "Database: Unable to set the configured connection charset ('{$this->charset}').");
|
||
|
||
$this->mysqli->close();
|
||
|
||
if ($this->DBDebug) {
|
||
throw new DatabaseException('Unable to set client connection character set: ' . $this->charset);
|
||
}
|
||
|
||
return false;
|
||
}
|
||
|
||
return $this->mysqli;
|
||
}
|
||
} catch (Throwable $e) {
|
||
// Clean sensitive information from errors.
|
||
$msg = $e->getMessage();
|
||
|
||
$msg = str_replace($this->username, '****', $msg);
|
||
$msg = str_replace($this->password, '****', $msg);
|
||
|
||
throw new DatabaseException($msg, $e->getCode(), $e);
|
||
}
|
||
|
||
return false;
|
||
}
|
||
|
||
/**
|
||
* Keep or establish the connection if no queries have been sent for
|
||
* a length of time exceeding the server's idle timeout.
|
||
*
|
||
* @return void
|
||
*/
|
||
public function reconnect()
|
||
{
|
||
$this->close();
|
||
$this->initialize();
|
||
}
|
||
|
||
/**
|
||
* Close the database connection.
|
||
*
|
||
* @return void
|
||
*/
|
||
protected function _close()
|
||
{
|
||
$this->connID->close();
|
||
}
|
||
|
||
/**
|
||
* Select a specific database table to use.
|
||
*/
|
||
public function setDatabase(string $databaseName): bool
|
||
{
|
||
if ($databaseName === '') {
|
||
$databaseName = $this->database;
|
||
}
|
||
|
||
if (empty($this->connID)) {
|
||
$this->initialize();
|
||
}
|
||
|
||
if ($this->connID->select_db($databaseName)) {
|
||
$this->database = $databaseName;
|
||
|
||
return true;
|
||
}
|
||
|
||
return false;
|
||
}
|
||
|
||
/**
|
||
* Returns a string containing the version of the database being used.
|
||
*/
|
||
public function getVersion(): string
|
||
{
|
||
if (isset($this->dataCache['version'])) {
|
||
return $this->dataCache['version'];
|
||
}
|
||
|
||
if (empty($this->mysqli)) {
|
||
$this->initialize();
|
||
}
|
||
|
||
return $this->dataCache['version'] = $this->mysqli->server_info;
|
||
}
|
||
|
||
/**
|
||
* Executes the query against the database.
|
||
*
|
||
* @return false|mysqli_result
|
||
*/
|
||
protected function execute(string $sql)
|
||
{
|
||
while ($this->connID->more_results()) {
|
||
$this->connID->next_result();
|
||
if ($res = $this->connID->store_result()) {
|
||
$res->free();
|
||
}
|
||
}
|
||
|
||
try {
|
||
return $this->connID->query($this->prepQuery($sql), $this->resultMode);
|
||
} catch (mysqli_sql_exception $e) {
|
||
log_message('error', (string) $e);
|
||
|
||
if ($this->DBDebug) {
|
||
throw new DatabaseException($e->getMessage(), $e->getCode(), $e);
|
||
}
|
||
}
|
||
|
||
return false;
|
||
}
|
||
|
||
/**
|
||
* Prep the query. If needed, each database adapter can prep the query string
|
||
*/
|
||
protected function prepQuery(string $sql): string
|
||
{
|
||
// mysqli_affected_rows() returns 0 for "DELETE FROM TABLE" queries. This hack
|
||
// modifies the query so that it a proper number of affected rows is returned.
|
||
if ($this->deleteHack === true && preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $sql)) {
|
||
return trim($sql) . ' WHERE 1=1';
|
||
}
|
||
|
||
return $sql;
|
||
}
|
||
|
||
/**
|
||
* Returns the total number of rows affected by this query.
|
||
*/
|
||
public function affectedRows(): int
|
||
{
|
||
return $this->connID->affected_rows ?? 0;
|
||
}
|
||
|
||
/**
|
||
* Platform-dependant string escape
|
||
*/
|
||
protected function _escapeString(string $str): string
|
||
{
|
||
if (! $this->connID) {
|
||
$this->initialize();
|
||
}
|
||
|
||
return $this->connID->real_escape_string($str);
|
||
}
|
||
|
||
/**
|
||
* Escape Like String Direct
|
||
* There are a few instances where MySQLi queries cannot take the
|
||
* additional "ESCAPE x" parameter for specifying the escape character
|
||
* in "LIKE" strings, and this handles those directly with a backslash.
|
||
*
|
||
* @param list<string>|string $str Input string
|
||
*
|
||
* @return list<string>|string
|
||
*/
|
||
public function escapeLikeStringDirect($str)
|
||
{
|
||
if (is_array($str)) {
|
||
foreach ($str as $key => $val) {
|
||
$str[$key] = $this->escapeLikeStringDirect($val);
|
||
}
|
||
|
||
return $str;
|
||
}
|
||
|
||
$str = $this->_escapeString($str);
|
||
|
||
// Escape LIKE condition wildcards
|
||
return str_replace(
|
||
[$this->likeEscapeChar, '%', '_'],
|
||
['\\' . $this->likeEscapeChar, '\\%', '\\_'],
|
||
$str
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Generates the SQL for listing tables in a platform-dependent manner.
|
||
* Uses escapeLikeStringDirect().
|
||
*
|
||
* @param string|null $tableName If $tableName is provided will return only this table if exists.
|
||
*/
|
||
protected function _listTables(bool $prefixLimit = false, ?string $tableName = null): string
|
||
{
|
||
$sql = 'SHOW TABLES FROM ' . $this->escapeIdentifier($this->database);
|
||
|
||
if ((string) $tableName !== '') {
|
||
return $sql . ' LIKE ' . $this->escape($tableName);
|
||
}
|
||
|
||
if ($prefixLimit && $this->DBPrefix !== '') {
|
||
return $sql . " LIKE '" . $this->escapeLikeStringDirect($this->DBPrefix) . "%'";
|
||
}
|
||
|
||
return $sql;
|
||
}
|
||
|
||
/**
|
||
* Generates a platform-specific query string so that the column names can be fetched.
|
||
*
|
||
* @param string|TableName $table
|
||
*/
|
||
protected function _listColumns($table = ''): string
|
||
{
|
||
$tableName = $this->protectIdentifiers(
|
||
$table,
|
||
true,
|
||
null,
|
||
false
|
||
);
|
||
|
||
return 'SHOW COLUMNS FROM ' . $tableName;
|
||
}
|
||
|
||
/**
|
||
* Returns an array of objects with field data
|
||
*
|
||
* @return list<stdClass>
|
||
*
|
||
* @throws DatabaseException
|
||
*/
|
||
protected function _fieldData(string $table): array
|
||
{
|
||
$table = $this->protectIdentifiers($table, true, null, false);
|
||
|
||
if (($query = $this->query('SHOW COLUMNS FROM ' . $table)) === false) {
|
||
throw new DatabaseException(lang('Database.failGetFieldData'));
|
||
}
|
||
$query = $query->getResultObject();
|
||
|
||
$retVal = [];
|
||
|
||
for ($i = 0, $c = count($query); $i < $c; $i++) {
|
||
$retVal[$i] = new stdClass();
|
||
$retVal[$i]->name = $query[$i]->Field;
|
||
|
||
sscanf($query[$i]->Type, '%[a-z](%d)', $retVal[$i]->type, $retVal[$i]->max_length);
|
||
|
||
$retVal[$i]->nullable = $query[$i]->Null === 'YES';
|
||
$retVal[$i]->default = $query[$i]->Default;
|
||
$retVal[$i]->primary_key = (int) ($query[$i]->Key === 'PRI');
|
||
}
|
||
|
||
return $retVal;
|
||
}
|
||
|
||
/**
|
||
* Returns an array of objects with index data
|
||
*
|
||
* @return array<string, stdClass>
|
||
*
|
||
* @throws DatabaseException
|
||
* @throws LogicException
|
||
*/
|
||
protected function _indexData(string $table): array
|
||
{
|
||
$table = $this->protectIdentifiers($table, true, null, false);
|
||
|
||
if (($query = $this->query('SHOW INDEX FROM ' . $table)) === false) {
|
||
throw new DatabaseException(lang('Database.failGetIndexData'));
|
||
}
|
||
|
||
$indexes = $query->getResultArray();
|
||
|
||
if ($indexes === []) {
|
||
return [];
|
||
}
|
||
|
||
$keys = [];
|
||
|
||
foreach ($indexes as $index) {
|
||
if (empty($keys[$index['Key_name']])) {
|
||
$keys[$index['Key_name']] = new stdClass();
|
||
$keys[$index['Key_name']]->name = $index['Key_name'];
|
||
|
||
if ($index['Key_name'] === 'PRIMARY') {
|
||
$type = 'PRIMARY';
|
||
} elseif ($index['Index_type'] === 'FULLTEXT') {
|
||
$type = 'FULLTEXT';
|
||
} elseif ($index['Non_unique']) {
|
||
$type = $index['Index_type'] === 'SPATIAL' ? 'SPATIAL' : 'INDEX';
|
||
} else {
|
||
$type = 'UNIQUE';
|
||
}
|
||
|
||
$keys[$index['Key_name']]->type = $type;
|
||
}
|
||
|
||
$keys[$index['Key_name']]->fields[] = $index['Column_name'];
|
||
}
|
||
|
||
return $keys;
|
||
}
|
||
|
||
/**
|
||
* Returns an array of objects with Foreign key data
|
||
*
|
||
* @return array<string, stdClass>
|
||
*
|
||
* @throws DatabaseException
|
||
*/
|
||
protected function _foreignKeyData(string $table): array
|
||
{
|
||
$sql = '
|
||
SELECT
|
||
tc.CONSTRAINT_NAME,
|
||
tc.TABLE_NAME,
|
||
kcu.COLUMN_NAME,
|
||
rc.REFERENCED_TABLE_NAME,
|
||
kcu.REFERENCED_COLUMN_NAME,
|
||
rc.DELETE_RULE,
|
||
rc.UPDATE_RULE,
|
||
rc.MATCH_OPTION
|
||
FROM information_schema.table_constraints AS tc
|
||
INNER JOIN information_schema.referential_constraints AS rc
|
||
ON tc.constraint_name = rc.constraint_name
|
||
AND tc.constraint_schema = rc.constraint_schema
|
||
INNER JOIN information_schema.key_column_usage AS kcu
|
||
ON tc.constraint_name = kcu.constraint_name
|
||
AND tc.constraint_schema = kcu.constraint_schema
|
||
WHERE
|
||
tc.constraint_type = ' . $this->escape('FOREIGN KEY') . ' AND
|
||
tc.table_schema = ' . $this->escape($this->database) . ' AND
|
||
tc.table_name = ' . $this->escape($table);
|
||
|
||
if (($query = $this->query($sql)) === false) {
|
||
throw new DatabaseException(lang('Database.failGetForeignKeyData'));
|
||
}
|
||
|
||
$query = $query->getResultObject();
|
||
$indexes = [];
|
||
|
||
foreach ($query as $row) {
|
||
$indexes[$row->CONSTRAINT_NAME]['constraint_name'] = $row->CONSTRAINT_NAME;
|
||
$indexes[$row->CONSTRAINT_NAME]['table_name'] = $row->TABLE_NAME;
|
||
$indexes[$row->CONSTRAINT_NAME]['column_name'][] = $row->COLUMN_NAME;
|
||
$indexes[$row->CONSTRAINT_NAME]['foreign_table_name'] = $row->REFERENCED_TABLE_NAME;
|
||
$indexes[$row->CONSTRAINT_NAME]['foreign_column_name'][] = $row->REFERENCED_COLUMN_NAME;
|
||
$indexes[$row->CONSTRAINT_NAME]['on_delete'] = $row->DELETE_RULE;
|
||
$indexes[$row->CONSTRAINT_NAME]['on_update'] = $row->UPDATE_RULE;
|
||
$indexes[$row->CONSTRAINT_NAME]['match'] = $row->MATCH_OPTION;
|
||
}
|
||
|
||
return $this->foreignKeyDataToObjects($indexes);
|
||
}
|
||
|
||
/**
|
||
* Returns platform-specific SQL to disable foreign key checks.
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function _disableForeignKeyChecks()
|
||
{
|
||
return 'SET FOREIGN_KEY_CHECKS=0';
|
||
}
|
||
|
||
/**
|
||
* Returns platform-specific SQL to enable foreign key checks.
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function _enableForeignKeyChecks()
|
||
{
|
||
return 'SET FOREIGN_KEY_CHECKS=1';
|
||
}
|
||
|
||
/**
|
||
* Returns the last error code and message.
|
||
* Must return this format: ['code' => string|int, 'message' => string]
|
||
* intval(code) === 0 means "no error".
|
||
*
|
||
* @return array<string, int|string>
|
||
*/
|
||
public function error(): array
|
||
{
|
||
if (! empty($this->mysqli->connect_errno)) {
|
||
return [
|
||
'code' => $this->mysqli->connect_errno,
|
||
'message' => $this->mysqli->connect_error,
|
||
];
|
||
}
|
||
|
||
return [
|
||
'code' => $this->connID->errno,
|
||
'message' => $this->connID->error,
|
||
];
|
||
}
|
||
|
||
/**
|
||
* Insert ID
|
||
*/
|
||
public function insertID(): int
|
||
{
|
||
return $this->connID->insert_id;
|
||
}
|
||
|
||
/**
|
||
* Begin Transaction
|
||
*/
|
||
protected function _transBegin(): bool
|
||
{
|
||
$this->connID->autocommit(false);
|
||
|
||
return $this->connID->begin_transaction();
|
||
}
|
||
|
||
/**
|
||
* Commit Transaction
|
||
*/
|
||
protected function _transCommit(): bool
|
||
{
|
||
if ($this->connID->commit()) {
|
||
$this->connID->autocommit(true);
|
||
|
||
return true;
|
||
}
|
||
|
||
return false;
|
||
}
|
||
|
||
/**
|
||
* Rollback Transaction
|
||
*/
|
||
protected function _transRollback(): bool
|
||
{
|
||
if ($this->connID->rollback()) {
|
||
$this->connID->autocommit(true);
|
||
|
||
return true;
|
||
}
|
||
|
||
return false;
|
||
}
|
||
|
||
// --------------------------------------------------------------------
|
||
// 迅睿 DDL API（供 Fcms/Model/Table 等调用，便于多库驱动替换）
|
||
// --------------------------------------------------------------------
|
||
|
||
/**
|
||
* 规范化建表/DDL SQL（Install.sql 等以 MySQL 方言为源）
|
||
*
|
||
* @param string $sql
|
||
*
|
||
* @return string
|
||
*/
|
||
public function formatCreateSql($sql)
|
||
{
|
||
if ($sql === '' || $sql === null) {
|
||
return '';
|
||
}
|
||
|
||
// Install.sql 等以 MySQL 为源：兼容旧 CHARSET=utf8，并规整 ENGINE 多余空格
|
||
$sql = str_replace('ENGINE=InnoDB ', 'ENGINE=InnoDB ', $sql);
|
||
$sql = str_replace(
|
||
'CHARSET=utf8 ',
|
||
'CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ',
|
||
$sql
|
||
);
|
||
|
||
return trim($sql);
|
||
}
|
||
|
||
/**
|
||
* 生成「添加字段」SQL（表名占位符 {tablename}，供字段系统批量替换）
|
||
*
|
||
* @param string $name 字段名
|
||
* @param string $type 字段类型，如 VARCHAR(255)
|
||
* @param string $info 附加属性，如 NULL DEFAULT NULL
|
||
* @param string $note 字段注释（空则不输出 COMMENT）
|
||
*
|
||
* @return string
|
||
*/
|
||
public function sqlAddField($name, $type, $info, $note = '')
|
||
{
|
||
$sql = 'ALTER TABLE `{tablename}` ADD ' . $this->escapeIdentifiers($name)
|
||
. ' ' . $type . ' ' . $info;
|
||
$note = (string) $note;
|
||
if ($note !== '') {
|
||
$sql .= " COMMENT '" . str_replace("'", "''", $note) . "'";
|
||
}
|
||
|
||
return $sql;
|
||
}
|
||
|
||
/**
|
||
* 生成「修改字段」SQL（表名占位符 {tablename}）
|
||
*
|
||
* @param string $name
|
||
* @param string $type
|
||
* @param string $info
|
||
* @param string $note
|
||
*
|
||
* @return string
|
||
*/
|
||
public function sqlEditField($name, $type, $info, $note = '')
|
||
{
|
||
$id = $this->escapeIdentifiers($name);
|
||
$sql = 'ALTER TABLE `{tablename}` CHANGE ' . $id . ' ' . $id
|
||
. ' ' . $type . ' ' . $info;
|
||
$note = (string) $note;
|
||
if ($note !== '') {
|
||
$sql .= " COMMENT '" . str_replace("'", "''", $note) . "'";
|
||
}
|
||
|
||
return $sql;
|
||
}
|
||
|
||
/**
|
||
* 生成「删除字段」SQL（表名占位符 {tablename}）
|
||
*
|
||
* @param string $name
|
||
*
|
||
* @return string
|
||
*/
|
||
public function sqlDropField($name)
|
||
{
|
||
return 'ALTER TABLE `{tablename}` DROP ' . $this->escapeIdentifiers($name);
|
||
}
|
||
|
||
/**
|
||
* 生成「批量添加字段」SQL（一条 ALTER 多列 ADD）
|
||
*
|
||
* @param array $columns [['name'=>'', 'type'=>'', 'info'=>'', 'note'=>''], ...]
|
||
*
|
||
* @return string
|
||
*/
|
||
public function sqlAddFields(array $columns)
|
||
{
|
||
$parts = [];
|
||
foreach ($columns as $col) {
|
||
if (empty($col['name'])) {
|
||
continue;
|
||
}
|
||
$part = 'ADD ' . $this->escapeIdentifiers($col['name'])
|
||
. ' ' . ($col['type'] ?? '')
|
||
. ' ' . ($col['info'] ?? '');
|
||
$note = isset($col['note']) ? (string) $col['note'] : '';
|
||
if ($note !== '') {
|
||
$part .= " COMMENT '" . str_replace("'", "''", $note) . "'";
|
||
}
|
||
$parts[] = trim($part);
|
||
}
|
||
|
||
if ($parts === []) {
|
||
return '';
|
||
}
|
||
|
||
return 'ALTER TABLE `{tablename}` ' . implode(', ', $parts);
|
||
}
|
||
|
||
/**
|
||
* 生成「批量删除字段」SQL（一条 ALTER 多列 DROP）
|
||
*
|
||
* @param array $names 字段名列表
|
||
*
|
||
* @return string
|
||
*/
|
||
public function sqlDropFields(array $names)
|
||
{
|
||
$parts = [];
|
||
foreach ($names as $name) {
|
||
if ($name === '' || $name === null) {
|
||
continue;
|
||
}
|
||
$parts[] = 'DROP ' . $this->escapeIdentifiers($name);
|
||
}
|
||
|
||
if ($parts === []) {
|
||
return '';
|
||
}
|
||
|
||
return 'ALTER TABLE `{tablename}` ' . implode(', ', $parts);
|
||
}
|
||
|
||
/**
|
||
* 删除表
|
||
*
|
||
* @param string $table 表名（可含前缀）
|
||
* @param bool $ifExists 是否使用 IF EXISTS
|
||
*
|
||
* @return false|ResultInterface|bool
|
||
*/
|
||
public function dropTable($table, $ifExists = true)
|
||
{
|
||
$table = trim((string) $table, '`');
|
||
$sql = ($ifExists ? 'DROP TABLE IF EXISTS ' : 'DROP TABLE ')
|
||
. $this->escapeIdentifiers($table);
|
||
|
||
return $this->query($sql);
|
||
}
|
||
|
||
/**
|
||
* 修改字段
|
||
*
|
||
* @param string $table 表名（可含前缀）
|
||
* @param string $name 字段名
|
||
* @param string $type 字段类型，如 VARCHAR(50)
|
||
* @param string $info 附加属性，如 NOT NULL / DEFAULT NULL
|
||
* @param string $note 字段注释
|
||
*
|
||
* @return false|ResultInterface|bool
|
||
*/
|
||
public function editField($table, $name, $type, $info, $note)
|
||
{
|
||
$table = trim((string) $table, '`');
|
||
|
||
return $this->query(str_replace(
|
||
'{tablename}',
|
||
$table,
|
||
$this->sqlEditField($name, $type, $info, $note)
|
||
));
|
||
}
|
||
|
||
/**
|
||
* 添加字段
|
||
*
|
||
* @param string $table 表名（可含前缀）
|
||
* @param string $name 字段名
|
||
* @param string $type 字段类型
|
||
* @param string $info 附加属性
|
||
* @param string $note 字段注释
|
||
*
|
||
* @return false|ResultInterface|bool
|
||
*/
|
||
public function addField($table, $name, $type, $info, $note)
|
||
{
|
||
$table = trim((string) $table, '`');
|
||
|
||
return $this->query(str_replace(
|
||
'{tablename}',
|
||
$table,
|
||
$this->sqlAddField($name, $type, $info, $note)
|
||
));
|
||
}
|
||
|
||
/**
|
||
* 删除字段
|
||
*
|
||
* @param string $table 表名（可含前缀）
|
||
* @param string $name 字段名
|
||
*
|
||
* @return false|ResultInterface|bool
|
||
*/
|
||
public function dropField($table, $name)
|
||
{
|
||
$table = trim((string) $table, '`');
|
||
|
||
return $this->query(str_replace(
|
||
'{tablename}',
|
||
$table,
|
||
$this->sqlDropField($name)
|
||
));
|
||
}
|
||
|
||
/**
|
||
* 创建表
|
||
*
|
||
* @param string $table 表名（可含前缀）
|
||
* @param array $fields 字段定义：['字段名' => '类型及属性SQL'] 或 [0 => '完整列定义SQL']
|
||
* @param array $indexs 索引定义：['PRIMARY KEY (`id`)', 'KEY `name` (`name`)', ...]
|
||
* @param string $note 表注释
|
||
*
|
||
* @return false|ResultInterface|bool
|
||
*/
|
||
public function createTable($table, $fields, $indexs, $note)
|
||
{
|
||
$lines = [];
|
||
|
||
if (is_array($fields)) {
|
||
foreach ($fields as $name => $def) {
|
||
if (is_int($name)) {
|
||
$lines[] = $def;
|
||
} else {
|
||
$lines[] = $this->escapeIdentifiers($name) . ' ' . $def;
|
||
}
|
||
}
|
||
}
|
||
|
||
if (is_array($indexs)) {
|
||
foreach ($indexs as $idx) {
|
||
if ($idx) {
|
||
$lines[] = $idx;
|
||
}
|
||
}
|
||
}
|
||
|
||
$sql = 'CREATE TABLE IF NOT EXISTS ' . $this->escapeIdentifiers($table) . " (
|
||
" . implode(",\n", $lines) . "
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='"
|
||
. str_replace("'", "''", (string) $note) . "'";
|
||
|
||
return $this->query($sql);
|
||
}
|
||
|
||
/**
|
||
* 获取建表 SQL（CREATE TABLE IF NOT EXISTS ...）
|
||
*
|
||
* @param string $table 表名（可含前缀）
|
||
*
|
||
* @return string
|
||
*/
|
||
public function createTableSql($table)
|
||
{
|
||
$row = $this->query('SHOW CREATE TABLE ' . $this->escapeIdentifiers($table))->getRowArray();
|
||
if (! $row || empty($row['Create Table'])) {
|
||
return '';
|
||
}
|
||
|
||
return str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row['Create Table']);
|
||
}
|
||
|
||
/**
|
||
* 获取表完整字段信息（兼容 SHOW FULL COLUMNS 结果结构）
|
||
*
|
||
* @param string $table 表名（可含前缀）
|
||
*
|
||
* @return array
|
||
*/
|
||
public function showFullColunms($table)
|
||
{
|
||
return $this->query('SHOW FULL COLUMNS FROM ' . $this->escapeIdentifiers($table))->getResultArray();
|
||
}
|
||
|
||
/**
|
||
* 获取全部表状态（兼容 SHOW TABLE STATUS 结果结构：Name/Engine/Rows/...）
|
||
*
|
||
* @return array
|
||
*/
|
||
public function showTableStatus()
|
||
{
|
||
return $this->query('SHOW TABLE STATUS')->getResultArray();
|
||
}
|
||
|
||
/**
|
||
* 修复表
|
||
*/
|
||
public function repairTable($table)
|
||
{
|
||
return $this->query('REPAIR TABLE ' . $this->escapeIdentifiers($table));
|
||
}
|
||
|
||
/**
|
||
* 优化表
|
||
*/
|
||
public function optimizeTable($table)
|
||
{
|
||
return $this->query('OPTIMIZE TABLE ' . $this->escapeIdentifiers($table));
|
||
}
|
||
|
||
/**
|
||
* 刷新表
|
||
*/
|
||
public function flushTable($table)
|
||
{
|
||
return $this->query('FLUSH TABLE ' . $this->escapeIdentifiers($table));
|
||
}
|
||
|
||
/**
|
||
* 检查表，返回一行状态（如 Msg_text）
|
||
*
|
||
* @return array|null
|
||
*/
|
||
public function checkTable($table)
|
||
{
|
||
return $this->query('CHECK TABLE ' . $this->escapeIdentifiers($table))->getRowArray();
|
||
}
|
||
|
||
/**
|
||
* 修改表默认字符集/排序规则
|
||
*
|
||
* @param string $table
|
||
* @param string $charset 默认 utf8mb4
|
||
* @param string $collate 默认 utf8mb4_unicode_ci
|
||
*/
|
||
public function setTableCharset($table, $charset = 'utf8mb4', $collate = 'utf8mb4_unicode_ci')
|
||
{
|
||
$table = $this->escapeIdentifiers(trim((string) $table, '`'));
|
||
$charset = preg_replace('/[^a-z0-9_]/i', '', (string) $charset);
|
||
$collate = preg_replace('/[^a-z0-9_]/i', '', (string) $collate);
|
||
|
||
return $this->query(
|
||
'ALTER TABLE ' . $table . ' DEFAULT CHARSET=' . $charset . ' COLLATE ' . $collate
|
||
);
|
||
}
|
||
|
||
// --------------------------------------------------------------------
|
||
// 字段搜索 WHERE 表达式（供 Model::where_* 中转；未实现则走 Model 默认 MySQL 写法）
|
||
// --------------------------------------------------------------------
|
||
|
||
/**
|
||
* FIND_IN_SET 语义
|
||
*/
|
||
public function whereFindInSet($column, $value)
|
||
{
|
||
$column = (string) $column;
|
||
if (dr_is_numeric($value)) {
|
||
return 'FIND_IN_SET(' . intval($value) . ',' . $column . ')';
|
||
}
|
||
|
||
return 'FIND_IN_SET("' . dr_safe_replace($value) . '",' . $column . ')';
|
||
}
|
||
|
||
/**
|
||
* JSON 包含（多选/关联等）
|
||
*/
|
||
public function whereJson($table, $name, $value)
|
||
{
|
||
if (strpos($name, '`') === false) {
|
||
$name = $table ? '`' . $table . '`.`' . $name . '`' : '`' . $name . '`';
|
||
}
|
||
|
||
if (version_compare($this->getVersion(), '5.7.0') < 0) {
|
||
return $name . ' LIKE \'%"' . $value . '"%\'';
|
||
}
|
||
|
||
return "(CASE WHEN JSON_VALID({$name}) THEN JSON_CONTAINS ({$name}->'$[*]', '\"" . $value . "\"', '$') ELSE null END)";
|
||
}
|
||
|
||
// --------------------------------------------------------------------
|
||
// 聚合 SELECT（供 Model::select_agg / select_count / select_sum 中转）
|
||
// --------------------------------------------------------------------
|
||
|
||
/**
|
||
* 混合 SELECT：'form_id, COUNT(*) AS cnt, SUM(price) AS total'
|
||
*/
|
||
public function buildSelectAgg($select)
|
||
{
|
||
$parts = $this->_xrSplitSelectParts((string) $select);
|
||
$out = [];
|
||
foreach ($parts as $part) {
|
||
$part = trim($part);
|
||
if ($part === '') {
|
||
continue;
|
||
}
|
||
if (preg_match('/^(COUNT|SUM|AVG|MAX|MIN)\s*\(\s*(.+?)\s*\)\s*(?:AS\s+([`"]?[\w]+[`"]?))?\s*$/i', $part, $m)) {
|
||
$out[] = $this->buildSelectFunc(strtoupper($m[1]), trim($m[2]), isset($m[3]) ? trim($m[3], '`"[]') : '');
|
||
continue;
|
||
}
|
||
if (preg_match('/^(.+?)\s+AS\s+([`"]?[\w]+[`"]?)\s*$/i', $part, $m)) {
|
||
$out[] = $this->_xrQuoteSelectIdent(trim($m[1])) . ' AS ' . $this->escapeIdentifiers(trim($m[2], '`"[]'));
|
||
continue;
|
||
}
|
||
$out[] = $this->_xrQuoteSelectIdent($part);
|
||
}
|
||
|
||
return implode(', ', $out);
|
||
}
|
||
|
||
/**
|
||
* 单个聚合：COUNT/SUM/AVG/MAX/MIN
|
||
*/
|
||
public function buildSelectFunc($fn, $field, $alias = '')
|
||
{
|
||
$fn = strtoupper(trim((string) $fn));
|
||
$field = trim((string) $field);
|
||
$alias = trim((string) $alias, '`"[]');
|
||
if (! in_array($fn, ['COUNT', 'SUM', 'AVG', 'MAX', 'MIN'], true) || $field === '') {
|
||
return '';
|
||
}
|
||
$inner = ($field === '*') ? '*' : $this->_xrQuoteSelectIdent($field);
|
||
$sql = $fn . '(' . $inner . ')';
|
||
if ($alias !== '') {
|
||
$sql .= ' AS ' . $this->escapeIdentifiers($alias);
|
||
}
|
||
|
||
return $sql;
|
||
}
|
||
|
||
/**
|
||
* 列标识加引（table.field / *）
|
||
*/
|
||
protected function _xrQuoteSelectIdent($expr)
|
||
{
|
||
$expr = trim((string) $expr);
|
||
if ($expr === '' || $expr === '*') {
|
||
return $expr === '*' ? '*' : '';
|
||
}
|
||
$expr = trim($expr, '`"[]');
|
||
if (strpos($expr, '.') !== false) {
|
||
$bits = explode('.', $expr);
|
||
foreach ($bits as &$b) {
|
||
$b = $this->escapeIdentifiers(trim($b, '`"[]'));
|
||
}
|
||
|
||
return implode('.', $bits);
|
||
}
|
||
|
||
return $this->escapeIdentifiers($expr);
|
||
}
|
||
|
||
/**
|
||
* 按逗号拆 SELECT（忽略括号/引号内逗号）
|
||
*/
|
||
protected function _xrSplitSelectParts($select)
|
||
{
|
||
$parts = [];
|
||
$buf = '';
|
||
$depth = 0;
|
||
$len = strlen($select);
|
||
$quote = null;
|
||
for ($i = 0; $i < $len; $i++) {
|
||
$ch = $select[$i];
|
||
if ($quote !== null) {
|
||
$buf .= $ch;
|
||
if ($ch === $quote) {
|
||
if (($ch === "'" || $ch === '"') && $i + 1 < $len && $select[$i + 1] === $ch) {
|
||
$buf .= $select[++$i];
|
||
continue;
|
||
}
|
||
$quote = null;
|
||
}
|
||
continue;
|
||
}
|
||
if ($ch === "'" || $ch === '"' || $ch === '`') {
|
||
$quote = $ch;
|
||
$buf .= $ch;
|
||
continue;
|
||
}
|
||
if ($ch === '(') {
|
||
$depth++;
|
||
$buf .= $ch;
|
||
continue;
|
||
}
|
||
if ($ch === ')') {
|
||
$depth = max(0, $depth - 1);
|
||
$buf .= $ch;
|
||
continue;
|
||
}
|
||
if ($ch === ',' && $depth === 0) {
|
||
$parts[] = $buf;
|
||
$buf = '';
|
||
continue;
|
||
}
|
||
$buf .= $ch;
|
||
}
|
||
if (trim($buf) !== '') {
|
||
$parts[] = $buf;
|
||
}
|
||
|
||
return $parts;
|
||
}
|
||
}
|