MYSQL笔记120410

DELIMITER $$DROP PROCEDURE IF EXISTS `DelAndInsertRReceiveInvoice`$$CREATE PROCEDURE `DelAndInsertRReceiveInvoice`( IN fromDate DATE, OUT delCnt LONG, OUT insertCnt LONG )BEGIN    DELETE FROM r_receive_invoice WHERE DATE_FORMAT(`receive_date`,'%Y-%m-%d') >= fromDate or receive_date is null or receive_date = '';        SELECT ROW_COUNT() INTO delCnt;    INSERT INTO r_receive_invoice  (       select   receive_invoice.id,                    product_type.name,                    receive_invoice.supplier_name,                    receive_invoice.money,                    receive_invoice.status,                    CASE status                      WHEN 1 THEN '已收票'              WHEN 2 THEN '已退票'                     END AS invoice_status_name,                    receive_invoice.invoice_type,                    CASE invoice_type              WHEN 0 THEN '普通'              WHEN 1 THEN '增值税'            END AS invoice_type_name,                    receive_invoice.number,                    receive_invoice.receive_date,                    receive_invoice.back_date,                    receive_invoice.user_name,                    receive_invoice.text              from  receive_invoice                         left join product_type                     on receive_invoice.product_type_id = product_type.idwhere (DATE_FORMAT(receive_invoice.receive_date,'%Y-%m-%d') >= fromDate or receive_invoice.receive_date is null or receive_invoice.receive_date = ''));            SELECT ROW_COUNT() INTO insertCnt;END$$DELIMITER ;

修复表

OPTIMIZE TABLE target_table;
FLUSH TABLE target_table