使用mysql 自带的语句构建 多个更新条件 批量更新
public static function batch_update($table_name = '', $data = [], $field = '')
{
if (!
$table_name || !
$data || !
$field) {
return false;
}
else {
$sql =
'UPDATE ' .
$table_name;
}
$con = [];
$con_sql = [];
$fields = [];
if (!is_array(
$field)) {
$field = explode(
',',
$field);
}
foreach (
$data as $key =>
$value) {
$x =
0;
foreach (
$value as $k =>
$v) {
$con_sql[
$x] =
isset(
$con_sql[
$x]) ?
$con_sql[
$x] :
'';
if (!in_array(
$k,
$field) && !
isset(
$con[
$x]) &&
$x ==
0) {
$con[
$x] =
" SET `" .
$k .
"` = (CASE ";
$con_sql[
$x] =
'';
}
elseif (!in_array(
$k,
$field) && !
isset(
$con[
$x]) &&
$x >
0) {
$con[
$x] =
" `" .
$k .
"` = (CASE ";
}
if (!in_array(
$k,
$field)) {
$con_sql[
$x] .=
" WHEN ";
foreach (
$field as $i =>
$val) {
$temp =
$value[
$val];
if (
$i >
0) {
$con_sql[
$x] .=
" AND `" .
$val .
"` = " .
$temp;
}
else {
$con_sql[
$x] .=
" `" .
$val .
"` = " .
$temp;
}
}
$con_sql[
$x] .=
" THEN '" .
$v .
"'";
$x++;
}
}
foreach (
$field as $fk =>
$fv) {
$fields[
$fv] =
isset(
$fields[
$fv]) ?
$fields[
$fv] : [];
$temp =
$value[
$fv];
if (!in_array(
$temp,
$fields[
$fv])) {
$fields[
$fv][] =
$temp;
}
}
}
$num = count(
$con) -
1;
foreach (
$con as $key =>
$value) {
foreach (
$con_sql as $k =>
$v) {
if (
$k ==
$key &&
$key <
$num) {
$sql .=
$value .
$v .
' END),';
}
elseif (
$k ==
$key &&
$key ==
$num) {
$sql .=
$value .
$v .
' END)';
}
}
}
$sql .=
" WHERE ";
$i =
0;
foreach (
$fields as $fsk =>
$fsv) {
if (
$i >
0) {
$sql .=
" AND " .
$fsk .
" IN (" . implode(
',',
$fsv) .
")";
}
else {
$sql .=
$fsk .
" IN (" . implode(
',',
$fsv) .
")";
}
$i++;
}
$res = Db::name(
$table_name)->execute(
$sql);
return $res;
}
最后返回的$sql就是组合后的批量修改的sql语句，上文中用 thinkphp 5 提交sql，并返回处理的条数，可以根据自己情况改变改代码
$res = Db::name(
$table_name)->execute(
$sql);
return $res;
转载请注明原文地址: https://www.6miu.com/read-74930.html
