如何使用Bito高效率修正SQL-摩杜云开发者社区

编写大段大段的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();