Category Archives: MySQL

MySQL備份

完整備份:(t為資料庫名)
Mysql -uroot -pPassword  t -e “FLUSH TABLES”;  // 關閉所有開啟的表, 把記憶體中的快取寫入磁碟
Mysql -uroot -pPassword  t -e “FLUSH TABLES WITH READ LOCK”;  // 關閉所有開啟的表, 加只讀鎖, 防止新的寫入操作
MysqlAdmin -uroot -pPassword  flush-logs // 生成新的二進位制日誌檔案和下面的備份時間保持同步, 以後對資料庫的操作都會記錄這個檔案中
mysqldump -uroot -pPassword t  > t.sql  // 完整備份

Reference:

Get and Change Increment Index in MySQL

GEt current index

Change Index

Reference:

  1. https://stackoverflow.com/questions/15821532/get-current-auto-increment-value-for-any-table
  2. https://stackoverflow.com/questions/970597/change-auto-increment-starting-number

Where with Subquery

Laravel Example

SQL

 

Reference:

  1. https://justcode.ikeepstudying.com/2016/08/mysql%E5%85%A5%E9%97%A8-%E4%B9%9D-%E5%AD%90%E6%9F%A5%E8%AF%A2-subquery/
  2. https://ithelp.ithome.com.tw/articles/10032121

Order by Array

Sample

Reference

  1. https://stackoverflow.com/questions/8055138/mysql-sort-order-by-array-value
  2. https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_field
  3. Could be replaced with orderRaw()
    https://laravel.com/docs/8.x/queries

Sort by array values

Reference:

  1. https://stackoverflow.com/questions/8055138/mysql-sort-order-by-array-value

SQL Statements to get daily Report

 

Get first row of each hour

First, get min id of each hour

Complete

 

Calculate DB sizes of Mysql on Linux

 

Dump Partial data of each table

//先切換到bin資料夾,因為沒有設定環境變數path
cd c:\program files\mariadb 10.4\bin

//目前限制100筆資料
mysqldump -u root -p –single-transaction –opt –where=”1 limit 100″ huede_hai > d:\backup\dump_hai_20190719.sql

Stored Function (Routine)

Reference: