SQL SHELL.SCIPT語法讓所有table新增欄位和更新欄位 - lustan3216/BlogArticle GitHub Wiki

SELECT table_name FROM information_schema.tables where table_schema=‘food’;

以上這行意思是從database = food抓全部的table名稱

ALTER TABLE clients ADD (created_at datetime, updated_at datetime);

以上這行clients加入兩個欄位和形態

UPDATE clients SET created_at = FROM_UNIXTIME(created),updated_at = FROM_UNIXTIME(modified);

更新這個欄位

mysql -uroot -p -e "SELECT table_name FROM information_schema.tables where table_schema='test123'"  > /tmp/t

/tmp是暫存區 (但應該是指記憶體的暫存連資料夾都不存在) xxxxxx xxxx xxx > /tmp/t 可以把所有的table名稱塞進暫存區裡面但是因為是mysql語法所以會多存一行header 要把第一行刪掉

sed -i .bak '1d' /tmp/t

然後再用more

 more /tmp/t   就可以叫出剛剛暫存的table名稱

##下面這行是代表把所有table_name用 d 變數表示

for d in `cat /tmp/t` 
do
  echo "ALTER TABLE $d  ADD (created_at datetime, updated_at datetime);"
  echo "UPDATE $d SET create_at  = FROM_UNIXTIME(created), updated_at  = FROM_UNIXTIME(modified);"
done
##  $d用的時候要加錢號

##最後再進化一下要變成以下這樣把指令都存起來

>> /tmp/y 就是把指令存進 y檔裡面

rm -rf /tmp/y && for d in `cat /tmp/t`
do
  echo "ALTER TABLE $d  ADD (created_at datetime, updated_at datetime);" >> /tmp/y
  echo "UPDATE $d SET $d.create_at  = FROM_UNIXTIME($d.created), $d.updated_at  = FROM_UNIXTIME($d.modified);" >> /tmp/y      
done

把存在/tmp/y 裡面的sql command全部導入test123

mysql -uroot -p test123 < /tmp/y

終極版本因為要符合只有created_at沒有和created有的時候才執行所以多了條件式

rm -rf /tmp/y && for d in `cat /tmp/t`
do
  rm -rf /tmp/c && rm -rf /tmp/d && rm -rf /tmp/e && rm -rf /tmp/f

  $(mysql -u root datbase_name -se "SHOW COLUMNS FROM $d where field = 'created_at'" > /tmp/c)
  $(mysql -u root datbase_name -se "SHOW COLUMNS FROM $d where field = 'created'" > /tmp/d )
  $(mysql -u root datbase_name -se "SHOW COLUMNS FROM $d where field = 'updated_at'" > /tmp/e)
  $(mysql -u root datbase_name -se "SHOW COLUMNS FROM $d where field = 'modified'" > /tmp/f)
  count_c=`cat /tmp/c | wc -l`
  count_d=`cat /tmp/d | wc -l`
  count_e=`cat /tmp/e | wc -l`
  count_f=`cat /tmp/f | wc -l`
  if [ "$count_c" -eq "0" ] && [ "$count_d" -eq "0" ] ; then
    echo "ALTER TABLE $d  ADD created_at datetime NOT NULL;" >> /tmp/y    
  fi
  if [ "$count_e" -eq "0" ] && [ "$count_f" -eq "0" ]; then
    echo "ALTER TABLE $d  ADD updated_at datetime NOT NULL;" >> /tmp/y  
  fi
  if [ "$count_c" -eq "0" ] && [  "$count_d" -eq "1" ]; then
    echo "ALTER TABLE $d  ADD created_at datetime NOT NULL DEFAULT '1970-01-01 00:00:01';" >> /tmp/y
    echo "UPDATE $d SET $d.created_at  = FROM_UNIXTIME($d.created);" >> /tmp/y
  fi
  if [ "$count_e" -eq "0" ] && [ "$count_f" -eq "1" ]; then
    echo "ALTER TABLE $d  ADD updated_at datetime NOT NULL DEFAULT '1970-01-01 00:00:01';" >> /tmp/y
    echo "UPDATE $d SET $d.updated_at  = FROM_UNIXTIME($d.modified);" >> /tmp/y
  fi  
done

如果要用assign 的話，變數跟等號之間不能有空白 /tmp/c or /tmp/d .... 這些是一般檔案，非執行檔，所以要用 cat /tmp/c 才能去count 行數 $() 是執行語法 -a == && -o == ||