yii mysql 强制从库_YII 自封装的批量修改的mysql操作类

* Created by PhpStorm.

* User: yufen

* Date: 2018/8/31

* Time: 9:54*/namespace app\base;useyii\db\ExpressionInterface;useYii;

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 ($valinstanceof 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 ($valinstanceof 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();

}

}