[MySQL]兩個資料表同步寫入，發生錯誤則回滾

transactional php mssql mysql laravel

isthome 2018-04-19 15:51:06 ‧ 10334 瀏覽

分享至

各位好~
目前有兩個關聯資料表，已經簡化成：

1) main_table (#main_id , m_name)
2) sub_table (#sub_id ,fk_main_id ,s_name)

[SCHEMA]


#----資料表 main_table
CREATE TABLE `main_table` (
	`main_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`m_name` TEXT NOT NULL COMMENT '名稱' COLLATE 'utf8mb4_unicode_ci',
  
	PRIMARY KEY (`main_id`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

#----資料表 sub_table
CREATE TABLE `sub_table` (
	`sub_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`fk_main_id` INT(11) NOT NULL COMMENT '外鍵ID',
	`s_name` TEXT NOT NULL COMMENT '名稱' COLLATE 'utf8mb4_unicode_ci',
  
	PRIMARY KEY (`sub_id`),
	CONSTRAINT `FK_1` FOREIGN KEY (`fk_main_id`) REFERENCES `main_table` (`main_id`) ON UPDATE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

問題簡述

會先在 main_table 插入一筆資料，藉以取得新ID (使用 mysql_insert_id())
並用此外鍵新IDmain_id 去寫入資料表 sub_table 的fk_main_id欄位。

虛擬執行 SQL 語法大概是這樣

$main_id = DB::table('main_table')->insert_get_id(array('m_name' => 'AAAA'));
DB::table('sub_table')->insert(
  array(
    'fk_main_id' => $main_id,
    's_name' => 'AAAA_sub'
  )
);

//把以上轉成原生 SQL 語法

$sql = "INSERT INTO main_table (m_name) VALUES ('AAAA')";
$main_id = $conn->query($sql)->insert_id;
$sql2 = "INSERT INTO sub_table (fk_main_id, s_name) VALUES ({{$main_id}} , 'AAAA_sub')";

2.那麼，假設資料表 sub_table 有除錯機制，如果s_name為空值則無法寫入、寫入失敗。
若發現資料表 sub_table 無法順利新增，則資料表 main_table 的也會恢復為上一動，也就是剛剛 insert 到資料表 main_table 那筆資料會同時刪除(或解釋成是沒有執行插入的這個動作)

請問如果設計成交易模式(transaction)，發現錯誤使之可以 rollback 當作兩個表完全沒新增的話該如何解法?

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

暐翰

iT邦大師 1 級 ‧ 2018-04-19 16:13:06

作法:

把sql改成以下
並把你的main_table的m_name，
sub_table的s_name用參數帶進script

script:

    $stmt = $conn->prepare("
        begin ;
        set @FK_ID := 
        (SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DB名稱' AND   TABLE_NAME   = '表格名稱');

        INSERT INTO `main_table`
        (`m_name`)VALUES(:m_name);

        INSERT INTO `sub_table`
        (`fk_main_id`,`s_name`)VALUES(@FK_ID,:s_name);

        commit;                           
    ");
    $stmt->bindParam(':m_name', $m_name);
    $stmt->bindParam(':s_name', $s_name);
 
    // 執行SQL
    $m_name = "xxx";
    $s_name = "zzz";
    $stmt->execute();