mysql / mariadb 1 2 3 4 5 START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT; (ROLLBACK;) postgres 1 2 3 4 BEGIN; UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice'; COMMIT; (ROLLBACK;) sqlserver 1 2 3 4 5 BEGIN TRANSACTION UPDATE Customers SET Country = 'UKA' WHERE Country = 'UK' COMMIT; (ROLLBACK;)
通常update 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 $StrSQL = "update GVIS_zaiko " . "\n" . " set GVIS_No = '" . $mysqli->real_escape_string($InputParm_GVIS_No) . "' \n" . " , Toroku = '" . $mysqli->real_escape_string($InputParm_Toroku) . "' \n" . " , Massho = '" . $InputParm_Massho . "' \n" . " , Category1 = '" . $mysqli->real_escape_string($InputParm_Category1) . "' \n" . " , Category2 = '" . $mysqli->real_escape_string($InputParm_Category2) . "' \n" . " , Tehai = '" . $mysqli->real_escape_string($InputParm_Tehai) . "' \n" . " , MakerURL = '" . $mysqli->real_escape_string($InputParm_MakerURL) . "' \n" . " , ShanaiURL = '" . $mysqli->real_escape_string($InputParm_ShanaiURL) . "' \n" . " , Kazu = " . $InputParm_Kazu . " \n" . " , YoteiTnk = " . $InputParm_YoteiTnk . " \n" . " , ShutokuKng = " . $InputParm_ShutokuKng . " \n" . " , KoteiShisan = " . $InputParm_KoteiShisan . " \n" . " , ShokyakuHou = '" . $InputParm_ShokyakuHou . "' \n" . " , TaiyoNen = " . $InputParm_TaiyoNen_Nen . " \n" . " , ShokyakuRit = " . $InputParm_TaiyoNen_Rit . " \n" . " , JigyoWariai = " . $InputParm_JigyoWariai . " \n" . " , weight = " . $InputParm_weight . " \n" . " , electricity = " . $InputParm_electricity . " \n" . " , Biko = '" . $mysqli->real_escape_string($InputParm_Biko) . "' \n" . " , alwayson = " . $InputParm_alwayson . " \n" . " , upd_date = '" . $gv_DATE . "' \n" . " , upd_user = '" . $gv_user . "' \n" . " where SerialShubetsu = '" . $InputParm_shisanKBN . "' \n" . " and SerialSeq1 = " . $InputParm_Seq1 . " \n" . " and SerialSeq2 = " . $InputParm_Seq2 . " \n" . " ; " . "\n" blobのupdate 1 2 3 4 5 6 7 8 9 10 11 $StrSQL = "update GVIS_zaiko " . "\n" . " set BLOB_data" .$Youso. " = '" . $img_bin . "' \n" . " , BLOB_extent" .$Youso. " = '" . $img_ext . "' \n" . " , BLOB_medium" .$Youso. " = '" . $img_binMini . "' \n" . " , upd_date = '" . $gv_DATE . "' \n" . " , upd_user = '" . $gv_user . "' \n" . " where SerialShubetsu = '" . $_SESSION["ATT102-arg1"] . "' \n" . " and SerialSeq1 = " . $_SESSION["ATT102-arg2"] . " \n" . " and SerialSeq2 = " . $_SESSION["ATT102-arg3"] . " \n" . " ; " . "\n" 通常のdelete 1 2 3 4 5 6 7 $StrSQL = "delete GVIS_work " . " from GVIS_work ". " \n" . " where year(workPeriod) = " . $InputYyyy. " \n" . " and month(workPeriod) = " . $InputMm. " \n" . " and workPriority = " . intval($InputParm2) . " \n" . " ; ". " \n"
MariaDBのサービス起動チェック 504_nariDBcheck.sh 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 #!/bin/sh ## ------------------------------------------------------------------------- ## Script Name : 504_nariDBcheck.sh ## Created by : T.Naritomi ## on : 2009.11.26 ## Updated by : ## on : ## Parameters : ## Return Code : 0=Normal End ## Comments : ## ------------------------------------------------------------------------- ## ---detail---------------------------------------------------------------- CHECK_HOST=localhost RESLOG=/tmp/$$.txt TMP_FILE=/tmp/$$TMP.txt MAIL_TO=hogehoge@gavann-it.com MAIL_TITLE="auto)mysql response error nariDB on "${CHECK_HOST} ##------Check by mysql --------------------------------------- /usr/bin/mysql -h ${CHECK_HOST} -u nari -pXXX >> ${RESLOG} << _EOF show databases ; exit _EOF cat ${RESLOG} RES=`cat ${RESLOG} | grep nariDB_1st | wc -l` echo response=${RES} if [ $RES -ne 0 ] ; then echo ok else echo mysql response error echo "down node = nariDB " ${CHECK_HOST} >> ${TMP_FILE} /bin/mail -s "$MAIL_TITLE" $MAIL_TO < ${TMP_FILE} fi ##------ rm execute -------------------------------------- rm -f ${RESLOG} rm -f ${TMP_FILE} exit $? crontab登録内容 1 2 3 4 #Timely procdure 00,10,20,30,40,50 * * * * sync 08,18,28,38,48,58 6-21 * * * /bin/sh /gvis/script/504_nariDBcheck.sh > /dev/null
mysql例 castで文字列型を日付型にしてしまう 1 2 3 insert into board values( null,'testuser', 'testmail','testcomment', cast('2009-08-03 23:58:01' as datetime))