<?php
/**
* Created by PhpStorm.
* User: yufen
* Date: 2018/8/31
* Time: 9:54
*/ namespace app\base; use yii\db\ExpressionInterface;
use Yii; trait DB
{
private static function changeFormat($val)
{
$db = Yii::$app->db;
if (is_string($val)) {
$val = $db->quoteValue($val);
} elseif (is_float($val)) {
$val = \yii\helpers\StringHelper::floatToString($val);
} elseif ($val === false) {
$val = 0;
} elseif ($val === null) {
$val = 'NULL';
}
return $val;
} private static function batchUpsertColumns($table, $columns, $rows, $updateColumns)
{
if (empty($rows)) {
return '';
}
$db = Yii::$app->db;
$schema = $db->getSchema();
if (($tableSchema = $schema->getTableSchema($table)) !== null) {
$columnSchemas = $tableSchema->columns;
} else {
$columnSchemas = [];
} $values = [];
foreach ($rows as $row) {
$vs = [];
foreach ($row as $i => $value) {
if (isset($columns[$i], $columnSchemas[$columns[$i]])) {
$value = $columnSchemas[$columns[$i]]->dbTypecast($value);
}
$value = self::changeFormat($value);
$vs[] = $value;
}
$values[] = '(' . implode(', ', $vs) . ')';
} if (empty($values)) {
return '';
}
foreach ($columns as $i => $name) {
$columns[$i] = $schema->quoteColumnName($name);
}
$sql = sprintf('INSERT INTO %s %s VALUES %s', $schema->quoteTableName($table), '(' . implode(',', $columns) . ')', implode(', ', $values));
if (!is_array($updateColumns) || empty($updateColumns)) {
return $sql;
}
$sets = [];
foreach ($updateColumns as $key => $val) {
$val = isset($columnSchemas[$key]) ? $columnSchemas[$key]->dbTypecast($val) : $val;
if ($val instanceof ExpressionInterface) {
$placeholder = $db->getQueryBuilder()->buildExpression($val);
} else if (is_numeric($val)) {
$placeholder = is_float($val) ? \yii\helpers\StringHelper::floatToString($val) : $val;
} else {
$placeholder = $db->quoteValue($val);
}
$sets[] = $db->quoteColumnName($key) . '=' . $placeholder;
}
return $sql . ' ON DUPLICATE KEY UPDATE ' . implode(', ', $sets);
} private static function batchUpdateColumns($table, $columns, $condition, &$params)
{
if (empty($columns)) {
return '';
}
$db = Yii::$app->db;
$schema = $db->getSchema();
if (($tableSchema = $schema->getTableSchema($table)) !== null) {
$columnSchemas = $tableSchema->columns;
} else {
$columnSchemas = [];
}
$value = [];
foreach ($columns as $rows) {
$name = $rows['name'];
$target = $rows['target'];
$vs = [];
foreach ($rows['data'] as $key => $val) {
$val = isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($val) : $val;
$key = isset($columnSchemas[$target]) ? $columnSchemas[$target]->dbTypecast($key) : $key;
$key = self::changeFormat($key);
if ($val instanceof ExpressionInterface) {
$val = $db->getQueryBuilder()->buildExpression($val, $params);
} else {
$val = $db->getQueryBuilder()->bindParam($val, $params);
}
$vs[] = ' WHEN ' . $key . ' THEN ' . $val;
}
$value[] = $schema->quoteColumnName($name) . '= CASE ' . $schema->quoteColumnName($target) . implode('', $vs) . ' END ';
}
$sql = sprintf('UPDATE %s SET %s', $schema->quoteTableName($table), implode(',', $value));
$where = $db->getQueryBuilder()->buildWhere($condition, $params);
return $where === '' ? $sql : $sql . ' ' . $where;
} /**upsert的拓展,批量操作
* eg:
* $res = self::batchUpsert('table', ['cid', 'goods', 'created'], [
* [1, 'test1', time()],
* [2, 'test2', time()]
* ], [
* 'goods' => new Expression('VALUES(goods)'),
* 'created'=>time()
* ]);
*
* @param $table
* @param $columns
* @param $rows
* @param $updateColumns
* @return int
*/
public static function batchUpsert($table, $columns, $rows, $updateColumns)
{
$db = Yii::$app->db;
$table = $db->quoteSql($table);
$sql = self::batchUpsertColumns($table, $columns, $rows, $updateColumns);
return $db->createCommand()->setRawSql($sql)->execute();
} /**批量更改
* eg
*self::batchUpdate('shop', [
* ['name'=>'goods','target'=>'tid','data'=>['1'=>'认真的么','2'=>'你是认真的么','3'=>'你确定你是认真的么???']],
* ['name'=>'created','target'=>'tid','data'=>['1'=>$_SERVER['REQUEST_TIME'],'2'=>$_SERVER['REQUEST_TIME'],'3'=>$_SERVER['REQUEST_TIME']]]
* ],['in','tid',[1,2,3]]);
* @param $table
* @param $rows
* @param string $condition
* @param array $params
* @return int
*/
public static function batchUpdate($table, $rows, $condition = '', $params = [])
{
$db = Yii::$app->db;
$table = $db->quoteSql($table);
$sql = self::batchUpdateColumns($table, $rows, $condition, $params);
return $db->createCommand()->setRawSql($sql)->bindValues($params)->execute();
}
}
以上的类通过自封装的batchUpsert,batchUpdate,是在YII环境下封装的
batchUpsert的运用方法如下
$res = self::batchUpsert('shop', ['cid', 'goods', 'created'], [
[1, 'text1', time()],
[2, 'text1', time()],
[3, 'text1', time()]
], [
'goods' => new Expression('VALUES(goods)'),
'created' => $_SERVER['REQUEST_TIME']
]);batchUpdate的运用方法如下
self::batchUpdate('shop', [
['name'=>'goods','target'=>'tid','data'=>['1'=>'认真的么','2'=>'你是认真的么','3'=>'你确定你是认真的么???']],
['name'=>'created','target'=>'tid','data'=>['1'=>$_SERVER['REQUEST_TIME'],'2'=>$_SERVER['REQUEST_TIME'],'3'=>$_SERVER['REQUEST_TIME']]]
],['in','tid',[1,2,3]]);