编写大段大段的SQL后,执行报错,人工检查难以发现问题,这时可以使用bito协助修正。 在使用几次bito后,发现它能够准确修正问题,推荐使用。 在编写SQL后,执行时报错信息如下:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1,自己检查了几遍,都没看出问题来。 使用bito修改,瞬间解决问题。
如何使用bito?
将执行的SQL、报错信息发给bito,提出修正要求 如下:
请指出并改正下面sql,执行时报错信息如下:1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1,用中文回答 :
DELIMITER //
CREATE PROCEDURE insert_acc_adapter_data()
BEGIN
-- 声明变量
DECLARE i INT DEFAULT 1;
DECLARE total_iterations INT DEFAULT 1;
-- 生成1000条不同的数据并插入到表中
WHILE i <= total_iterations DO
-- 定义插入acc_adapter.abs_guaranty_car_info的sql、values
set @info_sql = '';
set @info_sql = CONCAT('INSERT INTO acc_adapter.abs_guaranty_car_info (id, batch_date, car_brand, car_colour, car_frame_no, car_model, car_nature, car_sale_price, car_series, car_type, commission_total_fee, created_date, drive_years, due_bill_no, energy_type, engine_no, fee_one, fee_two, financing, gps_fee_amount, gps_no, guarantee_type, guaranty_num, insurance_total_fee, insurance_type, last_modified_date, license_num, mileage, mortgage_order, mortgage_status, new_car_price, partner_no, pawn_price, production_date, project_no, purchase_place, purchase_tax, register_date, total_investment, total_trans_times, year_trans_times) VALUES ');
SET @info_values = '';
-- 构造100条数据的VALUES子句
SET @j = 1;
WHILE @j <= 1000 DO
-- 生成唯一的apply_no和due_bill_no
SET @apply_no = CONCAT('APPLY_', i * 1000 + @j);
SET @due_bill_no = CONCAT('APPLY_', i * 1000 + @j);
-- 构造一条数据acc_adapter.abs_guaranty_car_info的VALUES子句
SET @info_values = CONCAT(@info_values,
'(',
i * 1000 + @j,',',
'"2021-04-01",',
'"CAAU",',
'"car_colour",',
'"LNKACDEF0MA380749",',
'"car_model",',
'"融资租赁车分期",',
'49900.00', ',',
'"KYXB",',
'"新车",',
'0.00', ',',
'"2021-12-02 21:11:51.479240000",',
'"1",', -- drive_years
'"',@due_bill_no,'",',
'"energy_type",', -- energy_type
'"211000459YLE"', ',',
'NULL', ',',
'NULL', ',',
'"反租"', ',',
'0.00', ',',
'"gps_no",',
'"抵押担保"', ',',
'"DY-SGX023-0228-010817"', ',',
'3169.00', ',',
'"insurance_type",',
'"2022-07-21 15:43:06.379137000"', ',',
'"未知"', ',',
'NULL', ',',
'"mortgage_order"', ',',
'"办理完成"', ',',
'77000.00', ',',
'"10043"', ',',
'49900.00', ',',
'"2022-01-01"', ',',
'"WS10043190001"', ',',
'"purchase_place"', ',',
'4491.00', ',',
'"2022-01-01"', ',',
'57560.00', ',',
'0', ',',
'0',
')'
);
-- 将生成的values值拼接成999个 (value1, value2, value3, ..., valueN),……,(value1, value2, value3, ..., valueN)
IF @j < 1000 THEN
SET @info_values = CONCAT(@info_values, ',');
END IF;
SET @j = @j + 1;
END WHILE;
-- 拼接出批量插入的sql,如下
/*
INSERT INTO table_name (column1, column2, column3, ..., columnN)
VALUES
(value1, value2, value3, ..., valueN),
(value1, value2, value3, ..., valueN),
...
(value1, value2, value3, ..., valueN);
*/
-- 可以优化重复的sql
-- 组装SQL
SET @info_sql = CONCAT(@info_sql, @info_values);
-- 执行动态SQL
PREPARE info_stmt FROM @sql;
EXECUTE info_stmt;
DEALLOCATE PREPARE info_stmt;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL insert_acc_adapter_data();
使用bito修正SQL
以下是修改后的SQL语句:
DELIMITER //
CREATE PROCEDURE insert_acc_adapter_data()
BEGIN
-- 声明变量
DECLARE i INT DEFAULT 1;
DECLARE total_iterations INT DEFAULT 1;
-- 生成1000条不同的数据并插入到表中
WHILE i <= total_iterations DO
-- 定义插入acc_adapter.abs_guaranty_car_info的sql、values
SET @info_sql = '';
SET @info_sql = CONCAT('INSERT INTO acc_adapter.abs_guaranty_car_info (id, batch_date, car_brand, car_colour, car_frame_no, car_model, car_nature, car_sale_price, car_series, car_type, commission_total_fee, created_date, drive_years, due_bill_no, energy_type, engine_no, fee_one, fee_two, financing, gps_fee_amount, gps_no, guarantee_type, guaranty_num, insurance_total_fee, insurance_type, last_modified_date, license_num, mileage, mortgage_order, mortgage_status, new_car_price, partner_no, pawn_price, production_date, project_no, purchase_place, purchase_tax, register_date, total_investment, total_trans_times, year_trans_times) VALUES ');
SET @info_values = '';
-- 构造100条数据的VALUES子句
SET @j = 1;
WHILE @j <= 1000 DO
-- 生成唯一的apply_no和due_bill_no
SET @apply_no = CONCAT('APPLY_', i * 1000 + @j);
SET @due_bill_no = CONCAT('APPLY_', i * 1000 + @j);
-- 构造一条数据acc_adapter.abs_guaranty_car_info的VALUES子句
SET @info_values = CONCAT(@info_values,
'(',
i * 1000 + @j,',',
'"2021-04-01",',
'"CAAU",',
'"car_colour",',
'"LNKACDEF0MA380749",',
'"car_model",',
'"融资租赁车分期",',
'49900.00', ',',
'"KYXB",',
'"新车",',
'0.00', ',',
'"2021-12-02 21:11:51.479240000",',
'"1",', -- drive_years
'"',@due_bill_no,'",',
'"energy_type",', -- energy_type
'"211000459YLE"', ',',
'NULL', ',',
'NULL', ',',
'"反租"', ',',
'0.00', ',',
'"gps_no",',
'"抵押担保"', ',',
'"DY-SGX023-0228-010817"', ',',
'3169.00', ',',
'"insurance_type",',
'"2022-07-21 15:43:06.379137000"', ',',
'"未知"', ',',
'NULL', ',',
'"mortgage_order"', ',',
'"办理完成"', ',',
'77000.00', ',',
'"10043"', ',',
'49900.00', ',',
'"2022-01-01"', ',',
'"WS10043190001"', ',',
'"purchase_place"', ',',
'4491.00', ',',
'"2022-01-01"', ',',
'57560.00', ',',
'0', ',',
'0',
')'
);
-- 将生成的values值拼接成999个 (value1, value2, value3, ..., valueN),……,(value1, value2, value3, ..., valueN)
IF @j < 1000 THEN
SET @info_values = CONCAT(@info_values, ',');
END IF;
SET @j = @j + 1;
END WHILE;
-- 拼接出批量插入的sql,如下
/*
INSERT INTO table_name (column1, column2, column3, ..., columnN)
VALUES
(value1, value2, value3, ..., valueN),
(value1, value2, value3, ..., valueN),
...
(value1, value2, value3, ..., valueN);
*/
-- 可以优化重复的sql
-- 组装SQL
SET @info_sql = CONCAT(@info_sql, @info_values);
-- 执行动态SQL
PREPARE info_stmt FROM @info_sql;
EXECUTE info_stmt;
DEALLOCATE PREPARE info_stmt;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL insert_acc_adapter_data();
如何查看修改后的SQL与原SQL的差异?
可以使用beyond compare工具进行对比,这样可以从错误中积累经验。
总结
在编写大段大段SQL的时候,难免会复制粘贴然后修改,但是可能复制粘贴后没有修改全部内容,导致执行报错。 而面对几百行SQL,肉眼检查十分费劲,有可能把自己搞得头晕脑胀。 为避免出现此类问题,有以下技巧:
- 涉及替换字段时,使用工具查找替换,尽量不要人工一个一个替换,如可以把SQL复制到notepad++后查找替换,当然要确定替换字段的唯一性,不要把其他字段替换错了
- 出现问题后,尝试使用bito等gpt工具帮忙检查
bito是什么?
Bito是一个人工智能助手,可以回答各种问题、提供实时的信息和帮助。它可以用于语言翻译、语法纠正、知识查询、日历提醒、天气预报、计算数学问题等多种场景。Bito使用自然语言处理和机器学习技术,能够理解和处理人类的语言,并根据问题提供准确的答案和建议。无论您是需要学习、工作还是生活中的帮助,Bito都可以为您提供便捷的服务。
bito不一定修改正确
如下面的问题,使用bito修改,没有修改正确 提问如下:
下面代码执行提示报错信息是1583 - Incorrect parameters in the call to native function 'concat’,请你指出问题,并修正,用中文回答:
DELIMITER //
CREATE PROCEDURE insert_datas()
BEGIN
-- 声明变量
DECLARE i INT DEFAULT 1;
DECLARE total_iterations INT DEFAULT 1;
DECLARE project_no VARCHAR(20) DEFAULT 'WS10043190001';
WHILE i <= total_iterations DO
-- 生成1000条不同的数据并插入到表中
-- 定义插入acc_loan.loan_apply_guaranty_car的sql、values
SET @car_sql = '';
SET @car_sql = CONCAT('INSERT INTO acc_loan.loan_apply_guaranty_car (id, project_no, due_bill_no, apply_no, guaranty_num, guaranty_type_enum, man_evaluate, car_sale_price, new_car_price, purchase_tax, premium_amount, car_type, car_frame_no, engine_no, gps_fee_amount, license_num, cxdm, production_date, mileage, version, created_date, last_modified_date, car_use_purpose, car_brand, evaluate_price, add_service_insurance, archives_fee, compulsory_insurance, gps_plus_fee, other_fee, premium_installation_fee, service_fee, vehicle_and_vessel_tax, color, first_payment_amount, first_payment_percent, mortgage_register_date, intermediation_fee) VALUES ');
SET @car_values = '';
-- 构造1000条数据的VALUES子句
SET @j = 1;
WHILE @j <= 1000 DO
-- 生成唯一的apply_no和due_bill_no
SET @apply_no = CONCAT('APPLY_', i * 1000 + @j);
SET @due_bill_no = CONCAT('APPLY_', i * 1000 + @j);
-- 构造一条数据acc_loan.loan_apply_guaranty_car的VALUES子句,id、due_bill_no需要唯一,其他字段写死
SET @car_values = CONCAT(@car_values,
'(',
i * 1000 + @j,',', -- id
'"WS10043190001",', -- project_no
'"',@due_bill_no,'",', -- due_bill_no
'"', @apply_no,'",', -- apply_no
'"guaranty_num123,"', -- guaranty_num
'"CAR",', -- guaranty_type_enum
'10000.00,', -- man_evaluate
'196000.00,', -- car_sale_price
'218900.00,', -- new_car_price
'16752.00,', -- purchase_tax
'4548.00,', -- premium_amount
'"NEW_CAR",', -- car_type
'"202111301007",', -- car_frame_no
'"121205",', -- engine_no
'700.00,', -- gps_fee_amount
'"license_num123",', --license_num
'NULL,', -- cxdm
'"2020-10-10",', -- production_date
'NULL,', -- mileage
'1,', -- version
'"2023-03-28 11:18:33.566841000",', -- created_date
'"2023-03-28 11:19:31.876073000",', -- last_modified_date
'"PASSENGER_CAR",', -- car_use_purpose
'"test车辆品牌",', -- car_brand
'NULL,', -- evaluate_price
'NULL,', -- add_service_insurance
'NULL,', -- archives_fee
'NULL,', -- compulsory_insurance
'NULL,', -- gps_plus_fee
'NULL,', -- other_fee
'NULL,', -- premium_installation_fee
'NULL,', -- service_fee
'NULL,', -- vehicle_and_vessel_tax
'"锰石黑",', -- color
'98000.00,', -- first_payment_amount
'0.50,', -- first_payment_percent
'"2020-10-15",', --mortgage_register_date
'NULL', -- intermediation_fee
')'
);
-- 将生成的values值拼接成999个 (value1, value2, value3, ..., valueN),……,(value1, value2, value3, ..., valueN)
IF @j < 1000 THEN
-- @car_values
set @car_values = CONCAT(@car_values, ',');
END IF;
SET @j = @j + 1;
END WHILE;
-- 拼接出批量插入的sql,如下
/*
INSERT INTO table_name (column1, column2, column3, ..., columnN)
VALUES
(value1, value2, value3, ..., valueN),
(value1, value2, value3, ..., valueN),
...
(value1, value2, value3, ..., valueN);
*/
-- 向acc_loan.loan_apply_guaranty_car一次性插入千条数据
set @car_sql = CONCAT(@car_sql, @car_values);
PREPARE car_data FROM @car_sql;
EXECUTE car_data;
DEALLOCATE PREPARE car_data;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL insert_datas();
回答如下:
问题是在拼接SQL语句的时候,缺少了一个逗号。修正方法是在每个VALUES子句的末尾添加逗号。
修正后的代码如下:
DELIMITER //
CREATE PROCEDURE insert_datas()
BEGIN
-- 声明变量
DECLARE i INT DEFAULT 1;
DECLARE total_iterations INT DEFAULT 1;
DECLARE project_no VARCHAR(20) DEFAULT 'WS10043190001';
WHILE i <= total_iterations DO
-- 生成1000条不同的数据并插入到表中
-- 定义插入acc_loan.loan_apply_guaranty_car的sql、values
SET @car_sql = '';
SET @car_sql = CONCAT('INSERT INTO acc_loan.loan_apply_guaranty_car (id, project_no, due_bill_no, apply_no, guaranty_num, guaranty_type_enum, man_evaluate, car_sale_price, new_car_price, purchase_tax, premium_amount, car_type, car_frame_no, engine_no, gps_fee_amount, license_num, cxdm, production_date, mileage, version, created_date, last_modified_date, car_use_purpose, car_brand, evaluate_price, add_service_insurance, archives_fee, compulsory_insurance, gps_plus_fee, other_fee, premium_installation_fee, service_fee, vehicle_and_vessel_tax, color, first_payment_amount, first_payment_percent, mortgage_register_date, intermediation_fee) VALUES ');
SET @car_values = '';
-- 构造1000条数据的VALUES子句
SET @j = 1;
WHILE @j <= 1000 DO
-- 生成唯一的apply_no和due_bill_no
SET @apply_no = CONCAT('APPLY_', i * 1000 + @j);
SET @due_bill_no = CONCAT('APPLY_', i * 1000 + @j);
-- 构造一条数据acc_loan.loan_apply_guaranty_car的VALUES子句,id、due_bill_no需要唯一,其他字段写死
SET @car_values = CONCAT(@car_values,
'(',
i * 1000 + @j,',', -- id
'"WS10043190001",', -- project_no
'"',@due_bill_no,'",', -- due_bill_no
'"', @apply_no,'",', -- apply_no
'"guaranty_num123,"', -- guaranty_num
'"CAR",', -- guaranty_type_enum
'10000.00,', -- man_evaluate
'196000.00,', -- car_sale_price
'218900.00,', -- new_car_price
'16752.00,', -- purchase_tax
'4548.00,', -- premium_amount
'"NEW_CAR",', -- car_type
'"202111301007",', -- car_frame_no
'"121205",', -- engine_no
'700.00,', -- gps_fee_amount
'"license_num123",', --license_num
'NULL,', -- cxdm
'"2020-10-10",', -- production_date
'NULL,', -- mileage
'1,', -- version
'"2023-03-28 11:18:33.566841000",', -- created_date
'"2023-03-28 11:19:31.876073000",', -- last_modified_date
'"PASSENGER_CAR",', -- car_use_purpose
'"test车辆品牌",', -- car_brand
'NULL,', -- evaluate_price
'NULL,', -- add_service_insurance
'NULL,', -- archives_fee
'NULL,', -- compulsory_insurance
'NULL,', -- gps_plus_fee
'NULL,', -- other_fee
'NULL,', -- premium_installation_fee
'NULL,', -- service_fee
'NULL,', -- vehicle_and_vessel_tax
'"锰石黑",', -- color
'98000.00,', -- first_payment_amount
'0.50,', -- first_payment_percent
'"2020-10-15",', --mortgage_register_date
'NULL', -- intermediation_fee
'),'
);
-- 将生成的values值拼接成999个 (value1, value2, value3, ..., valueN),……,(value1, value2, value3, ..., valueN)
IF @j < 1000 THEN
-- @car_values
set @car_values = CONCAT(@car_values, ',');
END IF;
SET @j = @j + 1;
END WHILE;
-- 拼接出批量插入的sql,如下
/*
INSERT INTO table_name (column1, column2, column3, ..., columnN)
VALUES
(value1, value2, value3, ..., valueN),
(value1, value2, value3, ..., valueN),
...
(value1, value2, value3, ..., valueN);
*/
-- 向acc_loan.loan_apply_guaranty_car一次性插入千条数据
set @car_sql = CONCAT(@car_sql, @car_values);
PREPARE car_data FROM @car_sql;
EXECUTE car_data;
DEALLOCATE PREPARE car_data;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL insert_datas();