分享几个批量操作方法

分享 0 1627
J.
J.   会员 发布于:13个月前
悬赏:60飞吻
/**
* @notes: 批量添加
* @param array $data
* @return mixed
* @throws \Exception
*
* $data = [['id' => 1, 'name' => 'test1'], ['id' => 2, 'name' => 'test2']]
*
* batchInsert( $data)
*
* INSERT INTO `table`( 'id','name' ) values ('1','test1') , ('2','test2')
*
*/
function batchInsert(array $data)
{
if (count($data) == 0) {
throw new \Exception('参数错误');
}

$arrKeys = array_keys(reset($data));

$fields = implode(',', array_map(function ($value) {
return "`" . $value . "`";
}, $arrKeys));

foreach ($data as $key => $val) {
$arrValues[$key] = implode(',', array_map(function ($value) {
return "'" . $value . "'";
}, $val));
}

$values = "(" . implode(') , (', array_map(function ($value) {
return $value;
}, $arrValues)) . ")";

$sql = "INSERT INTO `%s`( %s ) values %s ";

$sql = sprintf($sql, $this->getSource(), $fields, $values);

//DI中注册的数据库服务名称为"db"
$result = $this->getDI()->get('db')->execute($sql);

if (!$result) {
throw new \Exception('批量添加失败');
}

return $result;
}

/**
* @notes: 批量删除
* @param $data
* @param $field
* @return mixed
* @throws \Exception
*
* $data = [['id' => 1], ['id' => 2]]
*
* batchDelete($data, 'id')
*
* DELETE FROM `table` WHERE `id` IN (`1`,`2`)
*/
function batchDelete($data, $field)
{
if (count($data) == 0 || !$field) {
throw new \Exception('参数错误');
}
$fields = implode(',', array_map(function ($value) {
return "'" . $value . "'";
}, $fields));

$sql = 'DELETE FROM `%s` WHERE `%s` IN (%s)';

$sql = sprintf($sql, $this->getSource(), $field, $fields);

$result = $this->getDI()->get('db')->execute($sql);

if (!$result) {
throw new \Exception('批量删除失败');
}
return $result;
}

/**
* @notes: 批量更新
* @param $data
* @param $field
* @param array $params
* @return mixed
* @throws \Exception
*
* $data = [
* ['id' => 1, 'parent_id' => 100, 'title' => 'A', 'sort' => 1],
* ['id' => 2, 'parent_id' => 100, 'title' => 'A', 'sort' => 3]
* ];
*
* batchUpdate($data, 'id', ['parent_id' => 100, 'title' => 'A']);
*
* UPDATE `table` SET
* `id` = CASE `id`
* WHEN '1' THEN '1'
* WHEN '2' THEN '2'
* END,
* `parent_id` = CASE `id`
* WHEN '1' THEN '100'
* WHEN '2' THEN '100'
* END,
* `title` = CASE `id`
* WHEN '1' THEN 'A'
* WHEN '2' THEN 'A'
* END,
* `sort` = CASE `id`
* WHEN '1' THEN '1'
* WHEN '2' THEN '3'
* END
* WHERE `id` IN ('1','2') AND `parent_id` = '100' AND `title` = 'A'
*
* $data = [
* ['id' => 1, 'sort' => 1],
* ['id' => 2, 'sort' => 2]
* ];
*
* batchUpdate($data, 'id');
*
* UPDATE `post` SET
* `id` = CASE `id`
* WHEN '1' THEN '1'
* WHEN '2' THEN '2'
* END,
* `sort` = CASE `id`
* WHEN '1' THEN '1'
* WHEN '2' THEN '3'
* END
* WHERE `id` IN ('1','2')
*
*/
function batchUpdate($data, $field, array $params = [])
{
if (count($data) == 0 || !$field || !is_array($params)) {
throw new \Exception('参数错误');
}

$updates = parseUpdate($data, $field);
$where = parseParams($params);

$fields = array_column($data, $field);
$fields = implode(',', array_map(function($value) {
return "'".$value."'";
}, $fields));

$sql = 'UPDATE `%s` SET %s WHERE `%s` IN (%s) %s';

$sql = sprintf($sql, $this->getSource(), $updates, $field, $fields, $where);

$result = $this->getDI()->get('db')->execute($sql);

if (!$result) {
throw new \Exception('批量更新失败');
}
return $result;
}

/**
* 将二维数组转换成CASE WHEN THEN的批量更新条件
* @param $data array 二维数组
* @param $field string 列名
* @return string sql语句
*/
function parseUpdate($data, $field)
{
$sql = '';
$keys = array_keys(current($data));
foreach ($keys as $column) {

$sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
foreach ($data as $line) {
$sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]);
}
$sql .= "END,";
}

return rtrim($sql, ',');
}

/**
* 解析where条件
* @param $params
* @return array|string
*/
function parseParams($params)
{
$where = [];
foreach ($params as $key => $value) {
$where[] = sprintf("`%s` = '%s'", $key, $value);
}

return $where ? ' AND ' . implode(' AND ', $where) : '';
}
回帖
  • 暂时没有人回复,你来做第一个吧

J. 会员

2018-04-09 加入 来自中国 西安

(这个人懒得留下签名)

活跃榜
大家好像都不是很活跃
本周热门
本周暂无热帖