Category Archives: MySQL

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:

Mysql dump and restore on windows

Steps:

  1. Dump
    1. cd c:\program files\mariadb 10.4\bin
    2. mysqldump -u root -p –all-databases > d:\backup\dump20190712.sql
      Notice:

      1. Execute this command in “command promp” not in “mysql client”
      2. No “;” in the end!
  2. Restore
    1. mysql u username p dbname < filename.sql
    2. mysql -u root -p < backup.sql

Reference:

  1. https://john-dugan.com/dump-and-restore-mysql-databases-in-windows/
  2. https://code.yidas.com/mysqldump/