Flutter应用批量更新MySQL数据库多行数据失败求助

问题概述 #

在Flutter应用中尝试批量更新MySQL数据库多行数据时失败，触发SQL语法错误。

错误信息 #

MySqlException (错误码1064 (42000)): SQL语法有误；请查阅对应MySQL服务器版本的手册，了解在'? WHERE doc = ? AND sku = ?'附近使用正确语法的方法。

相关代码 #

DocPage业务代码 #

import 'package:cedi/data/connect.dart';
import 'package:flutter/material.dart';

class DocPage extends StatefulWidget {
  const DocPage({super.key});

  @override
  State<DocPage> createState() => _DocPageState();
}

class _DocPageState extends State<DocPage> {
  static List<DocsPickers> _docs = [];

  List docs = [
    {
      "qty": "400,0000",
      "date": "2023-07-24 07:43:00",
      "doc": "5275",
      "picker": "51",
      "sku": "VS100511",
      "cont": "25J2"
    },
    {
      "qty": "1152,0000",
      "date": "2023-07-24 08:04:00",
      "doc": "5275",
      "picker": "51",
      "sku": "VS100512",
      "cont": "25J2"
    }
  ];

  Future<void> _dataDocs() async {
    final db = Database();
    await db.connect();

    for (var _doc in docs) {
      final date = DateTime.parse(_doc["date"]);
      final doc = _doc["doc"];
      final sku = _doc["sku"];

      final results = await db.query(
        'UPDATE table1 SET date = ? WHERE doc = ? AND sku = ?',
        [date.toIso8601String(), doc, sku],
      );

      // ignore: avoid_print
      print('Updated rows: ${results.affectedRows}');
    }

    await db.close();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      floatingActionButton: FloatingActionButton(
        onPressed: () {
          _dataDocs();
        },
      ),
      appBar: AppBar(),
    );
  }
}

class DocsPickers {
  String? sku;
  DateTime? date;
  int? doc;
  double? qty;

  DocsPickers({required this.sku, required this.doc, this.date, this.qty});
}

Database连接工具类代码 #

import 'package:mysql1/mysql1.dart';

class Database {
  late MySqlConnection _connection;

  Future<void> connect() async {
    _connection = await MySqlConnection.connect(
      ConnectionSettings(
        host: 'host',
        port: 3306,
        user: 'user',
        password: 'password',
        db: 'db',
      ),
    );
  }

  Future<Results> query(String sql, List<Object> list) async {
    return _connection.query(sql);
  }

  Future<void> close() async {
    await _connection.close();
  }
}

问题原因 #

Database类中的query方法接收了参数列表list，但并未将其传递给底层的_connection.query方法。导致SQL语句中的占位符?没有被实际参数替换，直接发送到MySQL服务器，触发语法错误。

修复方案 #

修改Database类的query方法，将参数列表传递给底层的query调用：

Future<Results> query(String sql, List<Object> list) async {
  return _connection.query(sql, list);
}

优化建议 #

如果需要批量更新大量数据，建议使用事务包裹所有更新操作，确保数据一致性的同时提升执行效率：

Future<void> _dataDocs() async {
  final db = Database();
  await db.connect();

  // 用事务包裹批量更新
  await db._connection.transaction((tx) async {
    for (var _doc in docs) {
      final date = DateTime.parse(_doc["date"]);
      final doc = _doc["doc"];
      final sku = _doc["sku"];

      final results = await tx.query(
        'UPDATE table1 SET date = ? WHERE doc = ? AND sku = ?',
        [date.toIso8601String(), doc, sku],
      );

      print('Updated rows: ${results.affectedRows}');
    }
  });

  await db.close();
}

注：若要避免直接访问私有成员_connection，可在Database类中新增事务封装方法。

内容的提问来源于stack exchange，提问作者Gekyo