protected function _createByMySQL()
{
# 先检查表是否存在
$table = $this->_link['table'];
$sql = "SHOW TABLES LIKE `{$table}`";
$rs = $this->_driver()->query($sql);
$has = $rs->num_rows ? true : false;
$rs->free();
if ($has) {
# 已经存在, 检查表结构
# 读取所有数据
$rs = $this->_driver()->query("select * from `{$table}`");
while ($row = $rs->fetch_assoc()) {
$key = $row['_key'];
unset($row['_key']);
try {
# 设置数据
parent::set($key, $row);
} catch (\Exception $e) {
Server::$instance->warn($e->getMessage());
}
}
unset($row);
$rs->free();
# 检查表结构是否变化过
$sql = "SHOW COLUMNS FROM `{$table}`";
$rs = $this->_driver()->query($sql);
$col = $this->_column;
$removedFields = [];
$changedFields = [];
while ($row = $rs->fetch_assoc()) {
$field = $row['Field'];
$oldType = $row['Type'];
if ($field === '_key') {
continue;
}
if (isset($col[$field])) {
# 此字段在字段设置里存在
list($newType, $newSize) = $col[$field];
$fieldChanged = false;
if ($oldType === 'text') {
# 超过 2000 则为 text 类型
if ($newType === \Swoole\Table::TYPE_STRING && $newSize > 2000) {
# 相同
} else {
$fieldChanged = true;
}
} elseif (preg_match('#(varchar|bigint|int)\\((\\d+)\\)#', $oldType, $m)) {
# int(10), varchar(255)
if ($newType === \Swoole\Table::TYPE_INT) {
if ($newSize > 10) {
if ($m[1] !== 'bigint') {
$fieldChanged = true;
}
} else {
if ($m[1] !== 'int') {
$fieldChanged = true;
}
}
} elseif ($newType === \Swoole\Table::TYPE_FLOAT) {
$fieldChanged = true;
} else {
# \Swoole\Table::TYPE_STRING
if ($m[1] !== 'varchar' || $newSize > 2000) {
$fieldChanged = true;
}
}
} elseif (preg_match('#decimal\\((\\d+),(\\d+)\\)#', $oldType, $m)) {
if ($newType !== \Swoole\Table::TYPE_FLOAT) {
$fieldChanged = true;
}
} else {
$fieldChanged = true;
}
if ($fieldChanged) {
$changedFields[$field] = $col[$field];
}
unset($col[$field]);
} else {
# 没有对应的字段, 则说明已经被删除
$removedFields[] = $field;
}
}
if ($col || $removedFields || $changedFields) {
# 有新增字段或移除的字段或修改的字段
$tmp = [];
if ($removedFields) {
# ALTER TABLE `adv` DROP `t`;
foreach ($removedFields as $field) {
$tmp[] = "DROP `{$col}`";
}
}
if ($changedFields) {
static::_mysqlBuilderFieldSQL($tmp, $changedFields, 1);
}
if ($col) {
static::_mysqlBuilderFieldSQL($tmp, $changedFields, 0);
}
if ($tmp) {
$sql = "ALTER TABLE `{$table}` " . implode(', ', $tmp);
Server::$instance->debug("change mysql table: {$sql}");
# 执行结构变更
$this->_driver()->query($sql);
}
}
} else {
# 不存在对应的表, 创建表格
$tmp = [];
static::_mysqlBuilderFieldSQL($tmp, $this->_column, 2);
# 构造SQL
$sql = "CREATE TABLE `{$table}` (`_key` VARCHAR(255) NOT NULL, " . implode(', ', $tmp) . ", PRIMARY KEY (`_key`)) ENGINE = InnoDB";
Server::$instance->debug("change mysql table: {$sql}");
$this->_driver()->query($sql);
}
return true;
}