Jex’s Note

Git 常用指令

(最後更新: 2016-04-27)

git add

加入空的目錄

建立 gitkeep.keep 在空的目錄

git branch

  • 都不加 : 顯示 local branch
  • -r : 顯示 remote branch
  • -a : 顯示所有 branch (local + remote)
  • -v : 顯示branch的詳細資料
  • --merged : 列出已經merge的branch
  • --no-merged : 列出尚未merge的branch

Rename branch

git branch -m {old_name} {new_name}

Rename current branch

git branch -m {new_name}

刪除branch

git branch -d developXD

git merge

讓 commit log 紀錄您是開分支出去再 merge 回來的。

git merge --no-ff develop

Undo 某一個 merged 但未 push 的 branch

有幾種方法

1) git reset --hard HEAD~1

2) 先用 git reflog 看 log, 再把 HEAD@{1} 的 SHA1 記下來 (不是 HEAD@{0} 的, 因為那是你目前的點, 我們要還原到前一動), 再執行 git reset --hard {SHA1}

3) 用 git reset --hard {COMMIT_ID} 到早一點的點, 然候再慢慢補齊後來的 commit

如果對結果不太確定的話, 建議先用目前的 branch 建一個測試用的 branch 練習, 確定結果符合你要的再做在原本的 branch

git rebase

rebase 基本操作概念

類似 merge, 但將兩個不同的 branch 合併成同一條線且不會有 merge commit, tig 的線圖也會比較漂亮

1) rebase master

git checkout feature
git rebase master

2) merge 回 master

git checkout master
git merge feature

rebase 其他指令

如果有發生 conflict 並修好後要繼續 rebase

git rebase --continue

git merge 與 git rebase 差異

兩者結果是一樣的,產生的 history 不同點 :

  • git merge
    • 當 dev merge master, dev 會在另一條線做 merge, 所以 history 會看起來比較亂
    • conflict 只需要處理一次
  • git rebase
    • 當 dev rebase master, dev 會接在 master 最後面, 即使 master 有新的 commit 也會接在最後面, 所以它的 history 會很漂亮, 就只有一條
    • conflict 需要一個一個處理, 假如 dev 有兩個新的 commit id, 就需要處理兩次
    • 或用 git pull --rebase 也能達到一樣效果

使用時機 :

git checkout

新增 develop branch 並且切換過去

$ git checkout -b develop

新增由 develop 分支出來的 myfeature

$ git checkout -b myfeature develop

使檔案回復成最近一次commit的狀態

$ git checkout -- test.php

強制回復己在 add 狀態被修改過的檔案, Untracked files 則不受影響

git checkout -f

所有track中且修改過的檔案回復成最近一次commit的狀態

git checkout .

切到某一個 commit

git checkout 62a4a5c9a6e8a323a1ea12ec54ac35da7ce1b662

從某個 commit 切回原本 branch

git checkout -
相當於 git checkout master (不一定是 master 取決於在哪一個 branch)

git commit

  • --amend : 修改最後一次commit message

git reset

  • --soft : 回復到之前的狀態, 但修改過的檔案仍不變
  • --hard : 回復到之前的狀態, 但修改過的檔案仍會保留

回復到某一個 commit id

git reset --hard {commit id}

回復上一個 commit (包含 merge) ,但條改的資料不保留,也就是回覆到上一個 commit 的初始狀態

git reset HEAD^ --hard

回復上一個 commit 但修改的資料保留下來

git reset HEAD^ --soft

回復到 origin/master 的狀態 (當 merge 或做了什麼後悔的事, 在還沒 push 前都可以使用此指令還原到 remote 的最新狀態)

git reset --hard origin/master

git reset HEAD^ --hard 如何救回

流程

git reflog                  # 會有你的每條 git 操作的 log, 左邊有 commit id, 記下你要回復的 commit id
git reset --hard bceefb7    # 這樣就可以救回了

如果不放心可以自行測試

$ git init
Initialized empty Git repository in .git/

$ echo "testing reset" > file1
$ git add file1
$ git commit -m 'added file1'
Created initial commit 1a75c1d: added file1
 1 files changed, 1 insertions(+), 0 deletions(-)
 create mode 100644 file1

$ echo "added new file" > file2
$ git add file2
$ git commit -m 'added file2'
Created commit f6e5064: added file2
 1 files changed, 1 insertions(+), 0 deletions(-)
 create mode 100644 file2

$ git reset --hard HEAD^
HEAD is now at 1a75c1d... added file1

$ cat file2
cat: file2: No such file or directory

$ git reflog
1a75c1d... HEAD@{0}: reset --hard HEAD^: updating HEAD
f6e5064... HEAD@{1}: commit: added file2

$ git reset --hard f6e5064
HEAD is now at f6e5064... added file2

$ cat file2
added new file

ref: stackoverflow

git revert

與 reset 不同的事他回復是會有紀錄的,log 會疊下去,所以如果你不小心 push 了一個 commit 到 master,你想回復,用 revert 會比 reset 簡單操作

回復上一個 commit

git revert HEAD

回復到某一個 commit id

git revert {commit_id}

將某一個已經 commit/merge 的 commit_id 回復

git revert --strategy resolve 305a9bd07ae40c585ac2f2761dea4374e7fee93e

回復後它會有一個 commit 紀錄, 如果想再把它加回來, 無法用 merge (Already up-to-date.), 但可以用 cherry-pick 加回來

git cherry-pick

將某一個 branch 的某一個 commit 的變動 merge 到另一條 branch

假如 develop 只想要 merge feature 的某一個 commit, 則需要先把 feature 的那條 commit id 記下來, 再切到 develop 做 cherry-pick

git checkout develop
git cherry-pick f08515bc579a06dd9c8bd7f2dfc30ad4d5a73646

git show

查看某一個 tag 的變動

git show v1.0.0

git log

  • --stat : 列出有增減的檔案
  • --oneline : 精簡的log,每一次的commit資訊為一行,只顯示前7碼的SHA1及message

最近一次 commit 改過的檔案

git log -n 1 --stat

最近一次 commit 的更改細節

git log -n 1 -p

git reflog

顯示 git 的每個 log (包括操作 command 及 commit 內容等等)

git diff

  • default or -- : 非 staged 裡的與目前版本的差異
  • --staged or --cached(1.6版前) : stage 裡的與目前版本的差異
  • HEAD^ : 比較上一個commit的差異
  • branch : 比較branch的差異
  • --stat : 查看差異的概要 (顯示一堆 +, -)
  • sha1..sha1 : 比較兩個commit差異

未加入 stage 時直接下 :

git diff qq.php

已加入 stage 要下 :

git diff HEAD qq.php

比較兩個 branch :

git diff develop master

git config

  • --list : 查看 config 內容,也可以用另種方式看 cat ~/.gitconfig
  • --unset : reset 某個 config. e.g. git config --global --unset {key}

git rm

  • --cache filename.php : 將已被 git 追蹤的檔案取消追蹤, 放心! 它並不會將檔案刪除, 而將狀態變成 Untracked
  • --cached filename.php : 結果似乎和 --cache 一樣

git push

強制 push, 之前的commit 紀錄會不見,只會有目前這個branch的commit 紀錄

git push origin master -f

建立遠端 branch

git branch dev          // 先建立 local branch
git push origin dev:refs/heads/dev

git pull

git pull --rebase 等於以下兩步

git fetch
git merge origin dev

git pull –rebase 會使 merge commit 不見, 改用 :

git fetch origin
git rebase -p origin/develop

在目前 branch 下 git pull origin {branch_name}: 代表將 {branch_name} (remote branch) merge 到目前這個 branch

git stash

放進暫存

git stash

查看暫存

git stash list

取出暫存

git stash pop

清空暫存

git stash clear

如果暫存有多個,可以指定要還原哪個commit ID

git stash [commit ID]

stash specific files

git add a1.go                   // files that You don't want to stash
git stash save --keep-index     // this command will stash the rest of files

git tag

查看目前 tag

git tag
v1.0.0

新增 tag

git tag -a v1.4 -m "my version 1.4"
git push --tags

git fetch

更新 git branch -r 的名單

git fetch

(建議) 更新 remote branch list (遠端已刪除的 branch 會刪除,同時也會更新新增的 remote branch)

git remote update origin --prune

更新 remote branch list (遠端已刪除的 branch 端不會刪除,只會更新新增的 remote branch)

git fetch --all

當要 merge 遠端 branch 前,先看修改了什麼,再merge

git fetch origin dev
tig
git merge origin dev

git remote

新增遠端 repo

git remote add origin https://git.heroku.com/my-app.git

查看remote有哪些branch

git branch -r
git remote show origin

有時候用 git branch -r, 查看 remote branch 時, 發現明明刪掉的 branch 為何還在? 使用 git remote prune origin 來更新暫存檔, 就可以取得最新的 remote branch list

git rev-parse

latest commit hash

git rev-parse HEAD

remote branch

create

git push origin develop:refs/heads/branch_to_create
git fetch origin
git branch --track branch_to_create origin/branch_to_create
git checkout branch_to_create

track

git fetch origin
git branch --track branch_to_track origin/branch_to_track

delete remote branch

git push origin --delete <branch_name>

rename

# 如果 branch 已存在要先 remove branch
git push origin --delete <branch_name>


git push origin develop:refs/heads/branch_to_rename            # 這步只是將遠端 develop copy 成 branch_to_rename, develop 還在
git fetch origin
git branch --track branch_to_rename origin/branch_to_rename
git checkout branch_to_rename
git push origin :refs/heads/develop
git branch -D develop

publish

git push origin branch_to_publish:refs/heads/branch_to_publish
git fetch origin
git branch -u origin/branch_to_publish branch_to_publish
git checkout branch_to_publish

其他常用指令

  • git blame test.qq : test.php這份檔案顯示每行的編輯者
  • git branch -r : 查看遠端 branch
  • git add -u : 自動將所有的檔案都放到 stage 等待 commit, 而重要的是被刪除過的檔案也會一起放進去, 不需用 git rm 一個一個點被刪除過的檔案, 很方便!
  • tig : 顯示樹狀
  • gitk : 顯示樹狀 (GUI version)

prune 是在 grb delete 後才需要做的

減少 Merge branch 'master' of XXX 這種多餘節點

加上--rebase :

git pull --rebase

若在不同的 branch 要接遠端的 master 更新的話,執行:

git pull --rebase origin master

不過使用--rebase是在沒有conflict的情況下使用,它並不會像git merge那樣聰明地處理conflict,原理是rebase並沒有參考parent節點做同步

遇到conflict作法

如果產生了衝突回復 pull 前的狀態 :

git rebase --abort

然候合併code與 master 同步 :

git merge origin master

但這樣就還是會產生 Merge branch 'master' of XXX 節點

ref : 小莊

更新從別人 fork 過來的 repository

IMAG1680.jpg

git remote add upstream git@...(略)...
git remote -v
git pull upstream master

註 :

git pull upstream master 也等於

git fetch upstream
git merge upstream/master

將 commit 的東西 merge 到 qa

commit 後copy commit id

git checkout qa
git pull
git cherry-pick d69ad27d92cad1021481ecab731c0cc6552432bf    // 只 merge 我剛修改的
git push
git log -1
git show 41e24b9349acc9bbc1b8853284866f498892ce4b // 查看此 commit 改了什麼

ref : 夯哥

查看 commit id 修改內容

git show 41e24b9349acc9bbc1b8853284866f498892ce4b

.gitignore not working

git rm -r --cached .
git add .
git commit -m ".gitignore is now working"

將 diff 以 vimdiff 方式顯示

git config --global diff.tool vimdiff       # 使用 difftool 就可以啟動 vimdiff
git config --global difftool.prompt false   # 執行上面指令, 會問你是否執行, 很麻煩所以關掉它
git config --global alias.d difftool        # 使用 `git d` 相當於 `git difftool`

Trouble shooting

git clone error

$ git clone https://github.com/jex-lin/conf.git
Cloning into 'conf'...
error: Problem with the SSL CA cert (path? access rights?) while accessing https://github.com/jex-lin/conf.git/info/refs
fatal: HTTP request failed

解決 :

git config --global http.sslVerify false

contributing

  1. Fork it
  2. Create your feature branch (git checkout -b my-new-feature)
  3. Commit your changes (git commit -am ‘Add some feature’)
  4. Push to the branch (git push origin my-new-feature)
  5. Create new Pull Request

如何寫 commit messages

1) Subject 與 body 以一行空格分開

Derezz the master control program

MCP turned out to be evil and had become intent on world domination.
This commit throws Tron's disc into MCP (causing its deresolution)
and turns it back into a chess game.

這樣git log --oneline or git shortlog就可以只顯示主旨

2) Subject 不要超過 50 字元, 開頭要大寫, 結尾不需句號

3) 簡明的 subject

Refactor subsystem X for readability
Update getting started documentation
Remove deprecated methods
Release version 1.0.0
也可以用以下的句子
If applied, this commit will refactor subsystem X for readability
If applied, this commit will update getting started documentation
If applied, this commit will remove deprecated methods
If applied, this commit will release version 1.0.0
If applied, this commit will merge pull request #123 from user/branch

4) Body 在 72 字元換行,確保加上 git 本身的 indent 每行可以在 80 字元內

5) Body 用來解釋 what changed and why,主角不是 how

Simplify serialize.h's exception handling

Remove the 'state' and 'exceptmask' from serialize.h's stream
implementations, as well as related methods.

As exceptmask always included 'failbit', and setstate was always
called with bits = failbit, all it did was immediately raise an
exception. Get rid of those variables, and replace the setstate
with direct exception throwing (which also removes some dead
code).

As a result, good() is never reached after a failure (there are
only 2 calls, one of which is in tests), and can just be replaced
by !eof().

fail(), clear(n) and exceptions() are just never called. Delete
them.

ref : http://chris.beams.io/posts/git-commit/

其他參考 message

Other bug fixes
    * Fixed an issue that of forcibly terminating while running Peel remoate application.
    * The latest Android security patch has been applied.
    * Device security has been further enhanced.
Fixed the XXX bug in YYY

該用 present tense 還是 past tense?

兩邊都有支持者, git 本身的專案的 message 是用 present tense, 但有人覺得 present tense 是告訴別人將要做什麼, 用 past tense 比較正確, 但查了一輪下來, 支持 present tense 的人比較多一些, 我的建議是取決於團隊的 style, 當多數人用哪個就用哪個

PHP Examples

轉換容量顯示方式 (ex: 15.31GB)

function reable_size($bytes)
{
    if ($bytes == 0)
    {
        return '0 MB';
    }

    $unit = array('B', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB');

    $show_size = $bytes / pow(1024, ($i = floor(log($bytes, 1024))));
    if (intval($show_size) == $show_size)
    {
        return $show_size . ' ' . $unit[$i];
    }

    return number_format($show_size, 2) . ' ' . $unit[$i];
}

IP:

function get_ip()
{
    $inspect_type = array(
        "HTTP_CLIENT_IP",
        "HTTP_X_FORWARDED_FOR",
        "HTTP_X_FORWARDED",
        "HTTP_X_CLUSTER_CLIENT_IP",
        "HTTP_FORWARDED_FOR",
        "HTTP_FORWARDED"
    );

    foreach ($inspect_type as $type)
    {
        if (array_key_exists($type, $_SERVER))
        {
            $ips = explode(",", $_SERVER[$type]);
            foreach ($ips as $ip)
            {
                 if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4 | FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE))
                 {
                     return $ip;
                 }
            }
        }
    }
    return $_SERVER["REMOTE_ADDR"];
}

產生唯一字串(共32個小寫英文及數字)

echo md5(uniqid(rand(), TRUE));

產生亂數陣列(每個數字唯一,srand指定亂數產生)

php > $a = range(0, 29);
php > srand(333);           <= 隨意指定隨機數字
php > shuffle($a);          <= 按照隨機數字做排序
php > echo print_r($a);
Array
(
    [0] => 26
    [1] => 21
    (..略..)
    [28] => 15
    [29] => 24
)

判斷是否為ajax 傳進來的值(而不是submit傳入)

if (isset($_SERVER['HTTP_X_REQUESTED_WITH']))
{
    if (mb_strtoupper($_SERVER['HTTP_X_REQUESTED_WITH']) === "XMLHTTPREQUEST")
    {
        // do something...
        exit;
    }
}

日期與時間

設定台北時區

date_default_timezone_set("Asia/Taipei");

現在的時間(秒)

time();                                         //13551194547

將日期時間轉換為秒

strtotime('05/29/2013');

獲取目前的日期與時間

date('Y-m-d H:i:s');                            //2012-12-11 02:55:08

日期加1天、加5個月

date('Y-m-d', strtotime('+1 day'));             //加一天
date('Y-m-d', strtotime('+5 month'));           //加五個月

將時間(秒)還原回日期與時間正常格式

strftime('%m/%d/%Y %H:%M:%S', 1283846400);      //09/07/2010 16:00:00

產生時間數字型態(同strtotime)

mktime(0, 0, 0, date('m'), date('d'), date('Y')) + (60 * 60 * 24 * $day_offset);

Timestamp 產生至小數位

microtime(true);    // 1504695682.0988

註: .0000 也不會出錯
1504695796.9996
1504695796.9998
1504695796.9999
1504695797.0001
1504695797.0003
1504695797.0004

1504695867.9995
1504695867.9997
1504695867.9999
1504695868
1504695868.0002
1504695868.0004
1504695868.0005

DateTime 產生至小數位(推薦此方法)

$now = DateTime::createFromFormat('U.u', number_format(microtime(true), 6, '.', ''));
echo $now->format('Y-m-d H:i:s.u')."<br>";

DateTime 產生至小數位 DateTime createFromFormat error: call to a member function format() on boolean

不要用 `$now = DateTime::createFromFormat('U.u', microtime(TRUE))`
當0000,  `$now->format('Y-m-d H:i:s.u')` 會得到 fatal error : call to a member function format() on boolean

即使用 `if (is_bool($now)) $now = DateTime::createFromFormat('U.u', $aux += 0.001)`
在超過 9999 有一段極短的時間日期會是 1970-01-01
    2017-08-25 03:06:59.999900
    2017-08-25 03:06:59.999900
    2017-08-25 03:06:59.999900
    1970-01-01 00:00:00.001000
    1970-01-01 00:00:00.002000
    1970-01-01 00:00:00.003000
    1970-01-01 00:00:00.004000

DateTime 使用 time zone

$timezone = new DateTimeZone("Asia/Taipei");
$time = new DateTime("now", $timezone);
$now = $time->format("Y-m-d H:i:s");

DateTime 使用 time zone 取前一分鐘

$timezone = new DateTimeZone("Asia/Taipei");
$time = new DateTime("1 minutes ago", $timezone);
$timestamp = $time->format("YmdHi");

Convert time and date from one time zone to another

$date = new DateTime('2000-01-01', new DateTimeZone('Pacific/Nauru'));
echo $date->format('Y-m-d H:i:sP') . "\n";

$date->setTimezone(new DateTimeZone('Pacific/Chatham'));
echo $date->format('Y-m-d H:i:sP') . "\n";

刪除test.txt檔再將日期寫入test.txt檔100000次

$ts = array_sum(explode(" ", microtime()));

unlink("./test.txt");
for ($i = 0; $i < 100000; ++$i) {
    file_put_contents("./test.txt", date("Y-m-d H:i:s"), FILE_APPEND);
}
echo array_sum(explode(" ", microtime())) - $ts, "\n";

FILE_APPEND參數可參考php manual

輸出 csv 供下載

產生時間做為檔名,並且開啟檔案,檔案若不存在會自行建立

$file_name = array_sum(explode(" ", microtime())) . '.csv';
$file_path = '/var/www/csv/' . $file_name;
$fp = fopen($file_path, 'w');

將陣列寫入csv檔

fputcsv($fp, array('field1', 'field2', 'field3');
foreach ($record as $row)
{
    fputcsv($fp, array($row[1], $row[2], $row[3]));
}
fclose($fp);

讓瀏覽器可以輸出下載

header('Content-Type: application/csv');
header("Content-Disposition: attachment; filename={$file_name}");
header('Pragma: no-cache');
readfile($file_path);

php 將陣列組回url

EX:

echo urldecode(http_build_query(array('a' => array(1, 2, 3))));

結果 :

a[0]=1&a[1]=2&a[2]=3

uids array :

$uids = ["1","2","4","5"];
echo urldecode(http_build_query(array('uids' => $uids)));

結果 :

uids[0]=1&uids[1]=2&uids[2]=4&uids[3]=5

顯示目錄結構

$dir = dir('/var/www/test');
while ($folder = $dir->read())
{
    if ($folder == '.' || $folder == '..')
    {
        continue;
    }
    echo "{$folder}\n";
}

php two files combine

$file1_path = '/home/jex_lin/test/qq';
$file2 = file_get_contents('/home/jex_lin/test/cc');
file_put_contents($file1_path, $file2, FILE_APPEND);

連接 DB 及測試是否正常

建立測試的 DB 及 Table, 並透過 PDO 執行測試結果

sql :

建立 testDB Database 及 tt Table, 及建立一筆測試資料

php :

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'root', '');
$stat = $db->prepare("Select * from tt");
$stat->execute();
$result = $stat->fetchAll();
print_r($result);

$stat = $db->prepare("SELECT * FROM users WHERE account = ? AND password = ?");
$stat->execute(array($account, $password));
$result = $stat->fetch(PDO::FETCH_ASSOC);  // 如果沒找到是 false,有的話是 array

Server push by http1.1 chunked (都由 server 推給 client)

view :

<html lang="zh-TW">
<head></head>
<body>
<img src="image server 的連結">
</body>
</html>

image server :

<?php
$boundary = 'thisIsTheBoundary';

header( "Content-Type: multipart/x-mixed-replace; boundary=\"$boundary\"\r\n" );

echo "--$boundary\n" ;

for( $i=1 ; $i<= 10 ; $i++ )
{
        echo 'Content-Type: image/jpeg'."\n\n";

        echo file_get_contents( "images/$i.jpg" );

        echo "\n--$boundary\n" ;

        flush();
        sleep(1);
}
?>

PHP Memory

function convert($size)
{
   $unit=array('b','kb','mb','gb','tb','pb');
   return @round($size/pow(1024,($i=floor(log($size,1024)))),2).' '.$unit[$i];
}
echo $this->convert(memory_get_usage(true)); // 123 kb

費式數列

function fibonacci($n,$first = 0,$second = 1)
{
    $fib = [$first,$second];
    for($i=1;$i<$n;$i++)
    {
        $fib[] = $fib[$i]+$fib[$i-1];
    }
    return $fib;
}

print_r($this->fibonacci(50));

寄信

PHP 原生 mail 函數是使用 sendmail 來寄送, 請先安裝 sendmail

$email = "jxxxlin@gmail.com";
$subject = "Test";
$msg = "Hello world!";
$header = "From: Jex Lin<jxxxlin@gmail.com>\r\n";
$header .= 'Reply-To: jxxxlin@gmail.com' . "\r\n";          // 預設收件者回覆的對像
$header .= 'Cc: jex@example.com' . "\r\n";                  // 副本, email 會出現在信的副件上
$header .= 'Bcc: jex@example.com' . "\r\n";                 // 也是副本, 但 email 不會出現在信的副件上
$header .= "Content-Type: text/html; charset=utf-8\r\n";    // 可使用 HTML Tag
if (mail($email, $subject, $msg, $header))
{
    echo "success";
}
else
{
    echo "fail";
}

mb_substr 問題

之前碰到一個問題, 在 apache 與 cli 下 mb_substr 的結果不一樣,

如果只是將解析出來的 string 在 log 印出來是長一樣的, 但是將它寫進 file 的話結果就不同了

以下兩個環境解析出來字串的差別, 幾乎長一樣, 不一樣的是在最後面的字元

  • apache : % lt<8b>^@~<8f>¡ÅU¼0^Nß|×ÈI
  • cli : % lt<8b>^@~<8f>¡ÅU¼0^Nß|

最後去查官方文件 string mb_substr ( string $str , int $start [, int $length = NULL [, string $encoding = mb_internal_encoding() ]] )

原來最後一個編碼的參數是抓 PHP 的系統參數, 然候我再把這個參數印出來看

  • apache : UTF-8
  • cli : ISO-8859-1

確認是採用的編碼不同, 造成字串截取的結果不一樣

解決的方法可以設定在系統變數, 或寫在 mb_substr 參數裡或直接設定 mb_internal_encoding('ISO-8859-1');

AES 加解密

  • 密鑰 : AES 使用對稱式密鑰,加解密都要用同一把密鑰
  • IV (Initialization Vector) : 避免加密結果每次都一樣
  • Paadding : 對未加密的資料做填充, 補滿 128 位元, 常見的方式為 PKCS5

example :

$iv_length = mcrypt_get_iv_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_CBC);
$iv = mcrypt_create_iv($iv_length, MCRYPT_RAND);
$key = 'jex';
$text = 'secret data';

# 加密
$encrypt = mcrypt_encrypt(MCRYPT_RIJNDAEL_128, $key, $text, MCRYPT_MODE_CBC, $iv);
echo base64_encode($encrypt) . "\n";        // 0gSYCbCcf/u1S78uIVXWZw==

# 解密
$decrypt = mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $key, $encrypt, MCRYPT_MODE_CBC, $iv);
echo $decrypt . "\n";                       // secret data

JSONP

protected function _outputJSON($data)
{
    $callback = $this->input->get_post('callback', TRUE);
    if (empty($callback))
    {
        header("Content-type: application/json");
    }
    else
    {
        header('Content-Type: application/javascript');
    }
    $json_str = json_encode($data);
    echo (empty($callback)) ? $json_str : $callback . "(" . $json_str . ")";
    exit;
}

linux 檔案脫逸,支援 windows 支援的符號(也就是除了 \ / : * ? “ < > |)

private function _escape_cmd_argv($path)
{
    $path = str_replace(
        [ ' ', '"',  '`',  '^',  '!',  '$',  '&',  '(',  ')',  '[',  ']',  '{',  '}',  ',',  ';',   "'"],
        ['\ ','\"', '\`', '\^', '\!', '\$', '\&', '\(', '\)', '\[', '\]', '\{', '\}', '\,', '\;', "\\'"],
        $path
    );
    return $path;
}

將 JSON 輸出在 html hidden 欄位裡

php :

$convenience_store = [
    array(
        'value'     => 70,
        'label'     => '7-11',
        'formatted' => '12552 間',
    ),
];

html :

<input type="hidden" id="getData" value=""<?php echo htmlspecialchars($convenience_store, ENT_QUOTES); ?>"/>

注意 htmlspecialcharsENT_QUOTES 參數

js :

var getData = JSON.parse($('#getData').val());

output mp4 - support range

<?php
$file = 'test.mp4';
$fp = @fopen($file, 'rb');
$size   = filesize($file); // File size
$length = $size;           // Content length
$start  = 0;               // Start byte
$end    = $size - 1;       // End byte
header('Content-type: video/mp4');
//header("Accept-Ranges: 0-$length");
header("Accept-Ranges: bytes");
if (isset($_SERVER['HTTP_RANGE'])) {
    $c_start = $start;
    $c_end   = $end;
    list(, $range) = explode('=', $_SERVER['HTTP_RANGE'], 2);
    if (strpos($range, ',') !== false) {
        header('HTTP/1.1 416 Requested Range Not Satisfiable');
        header("Content-Range: bytes $start-$end/$size");
        exit;
    }
    if ($range == '-') {
        $c_start = $size - substr($range, 1);
    }else{
        $range  = explode('-', $range);
        $c_start = $range[0];
        $c_end   = (isset($range[1]) && is_numeric($range[1])) ? $range[1] : $size;
    }
    $c_end = ($c_end > $end) ? $end : $c_end;
    if ($c_start > $c_end || $c_start > $size - 1 || $c_end >= $size) {
        header('HTTP/1.1 416 Requested Range Not Satisfiable');
        header("Content-Range: bytes $start-$end/$size");
        exit;
    }
    $start  = $c_start;
    $end    = $c_end;
    $length = $end - $start + 1;
    fseek($fp, $start);
    header('HTTP/1.1 206 Partial Content');
}
header("Content-Range: bytes $start-$end/$size");
header("Content-Length: ".$length);
$buffer = 1024 * 8;
while(!feof($fp) && ($p = ftell($fp)) <= $end) {
    if ($p + $buffer > $end) {
        $buffer = $end - $p + 1;
    }
    set_time_limit(0);
    echo fread($fp, $buffer);
    flush();
}
fclose($fp);
exit();
?>

Ref : http://www.tuxxin.com/php-mp4-streaming/

Send a notification to android device.

define("GOOGLE_API_KEY", "***************************************");
define("GOOGLE_GCM_URL", "https://android.googleapis.com/gcm/send");

function send_push_notification($registatoin_ids, $message)
{
    $fields = array(
            'registration_ids' => $registatoin_ids,
            'data' => array('message'=>$message)
            );

    $headers = array(
            'Authorization: key=' . GOOGLE_API_KEY,
            'Content-Type: application/json'
            );

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, GOOGLE_GCM_URL);
    curl_setopt($ch, CURLOPT_POST, TRUE);
    curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($fields));
    $result = curl_exec($ch);
    if ($result === FALSE) {
        return array(FALSE, 'curl error : ' . curl_error($ch));
    }
    curl_close($ch);
    $result_arr = json_decode($result, TRUE);
    if ( ! $result_arr)
    {
        return array(FALSE, 'json err : ' . $result);
    }
    if ($result_arr['success']=='1')
    {
        return array(TRUE, 'Message successfully delivered');
    }
    else
    {
        return array(FALSE, json_encode($result_arr['results']));
    }
}

/*
$regids = array('*********************************-Cv4vK8p54hH674CHvCfHQq0_dAw749k********************k6cuKsO_O_riIx_EyFGZEgNrHA**********************');
$message = 'hello world ' . date('Y-m-d H:i:s');

echo "start\n";
list($s, $e) = send_push_notification($regids, $message);
var_dump($s);
echo $e . "\n";

*/

Send a notification to ios device.

function send_notify2APNS($deviceToken, $message, $env = 'development', $debug=FALSE)
{

    log_message("INFO", "token is : " . $deviceToken);
        $passphrase = '';

        $fp  = FALSE;
        $ctx = stream_context_create();
        if ($env == 'development')
        {
            $pem_file = '/home/qq/pem/jemco_dev_push.pem';
            stream_context_set_option($ctx, 'ssl', 'local_cert', $pem_file);
            stream_context_set_option($ctx, 'ssl', 'passphrase', $passphrase);
            $fp = stream_socket_client(
                'ssl://gateway.sandbox.push.apple.com:2195', $err,
                $errstr, 60, STREAM_CLIENT_CONNECT|STREAM_CLIENT_PERSISTENT, $ctx);
        }
        else
        {
            return array(FALSE, 'Env is not development');
        }

        if (!$fp)
        {
            return array(FALSE, "Failed to connect: $err $errstr");
        }
        $body['aps'] = array(
                'alert' => $message,
                'sound' => 'default'
                );
        $payload = json_encode($body);
        $msg = chr(0) . pack('n', 32) . pack('H*', $deviceToken) . pack('n', strlen($payload)) . $payload;
        $result = fwrite($fp, $msg, strlen($msg));
        fclose($fp);

        return ($result) ? array(TRUE, 'Message successfully delivered') : array(FALSE, 'Message not delivered');
}
/*
$deviceToken = '6529a573393c*********************************27639b';
$message = file_get_contents('http://whatthecommit.com/index.txt');
list($s, $m) = send_notify2APNS($deviceToken, $message);
echo $m . "\n";
*/

php array 處理

確保 json_encode 後不會有索引

array_unique 做 json_encode 時,會得到索引->值, 當其他強型態語言(e.g. golang)在事先指定只接收 array 的話,在 decode 時會出錯, 可以用此方法做 unique 但 encode 不會有 key

array_keys(array_flip($test_array))

array_map(‘strval’, 將 value 都轉成 string,避免值是 int 造成其他強型態語言錯誤

array_map('strval', array_keys(array_flip($test_array)))

curl 上傳

php 5.6 用 @

'image' => '@' . $_FILES['image']['tmp_name']   // e.g. @/tmp/phpDV7N6h

php 7.0 用 CURLFile

'image' => new CURLFile($_FILES['image']['tmp_name'], 'image/jpeg', $_FILES['image']['name']),

code

$post_data = [
    'image' => new CURLFile($_FILES['image']['tmp_name'], 'image/jpeg', $_FILES['image']['name']),
];
$ch = curl_init();
curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER  => 1,       // return response body
        CURLOPT_TIMEOUT         => 10,
        CURLOPT_URL             => "http://127.0.0.1:8080/user/avatar/upload",
        CURLOPT_POST            => 1,
        CURLOPT_POSTFIELDS      => $post_data,
    ]);
$resp_body = curl_exec($ch);
$httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close ($ch);
if ($httpcode == 200)
{
    // do something
}

Apache

基本設定

/etc/apache2/sites-available/jex.conf :

<VirtualHost *:80>
        ServerName example.com
        ServerAdmin admin@example.com

        DocumentRoot /var/www/jex
        <Directory /var/www/jex>
                RewriteEngine on
                RewriteCond $1 !^(index\.php|static|crossdomain\.xml|robots\.txt|favicon\.ico)
                RewriteRule ^(.*)$ /index.php/$1 [L]

                Options Indexes FollowSymLinks MultiViews
                AllowOverride All
                Order allow,deny
                allow from all

                ExpiresActive On
                ExpiresByType image/jpg "access plus 10 years"
                ExpiresByType image/jpeg "access plus 10 years"
        </Directory>
        ErrorLog ${APACHE_LOG_DIR}/error.log
        CustomLog ${APACHE_LOG_DIR}/access.log combined
        LogLevel warn
</VirtualHost>

啟動 conf

sudo a2ensite jex

Options

  • All: 以下諸功能皆有
  • None: 以下諸功能皆無
  • Indexes: 自動產生目錄的索引, 將Indexes拿掉, 點選連結目錄就不會秀出目錄來, 會改成403 forbidden的訊息頁面, 如果網站下的 public 下有放 symbolic link 就要將此開啟, 才可以被 access
  • Includes: 提供 SSI (Server-Side Inclues) 功能, 即使用Apache的指令在html檔中寫程式, 須先載入 includes_module
  • FollowSymLinks: 遵循符號鏈接, 即能夠連到其它的目錄去執行, 會壓過 SymLinksIfOwnerMatch。
  • SymLinksIfOwnerMatch: 對符號鏈接及其每一層父資料夾, 都進行權限檢查, 當連結檔本身的owner跟連結目的地的owner不同時拒絕存取, 比 FollowSymLinks 更安全
  • ExecCGI: 可以執行CGI程式。
  • MultiViews: 送出多國語言支援的頁面. 此功能必須被明確指定, Options All並不會提供這個功能

打開 Rewrite 模組

cd /etc/apache2
a2enmod
rewrite

設置轉向自訂的404頁面

ErrorDocument 404 /404.php

建立多個virtual host

  1. /etc/apache2/sites-available建立virtual host, ex: test
  2. sudo a2ensite test
  3. sudo service apache2 restart

設定環境變數

多用在不同的virtual host使得每個都擁有不同的環境變數例如:

不同的virtual host要共用同一個環境變數可以在/etc/apache2/apache2.conf設定

SetEnv BRAND test

各別的virtual host變數可在/etc/apache2/sites-available/ 設定:

  <VirtualHost *:80>
    <Directory /var/www>
    ...
    SetEnv BRAND test[n]
    ...
    </Directory>
  </VirtualHost>

php取得apache環境變數:

  echo getenv('BRAND');

如果網址只指到資料夾,不顯示目前資料夾的檔案列表

<VirtualHost *:80>
    Options -Indexes FollowSymLinks
</VirtualHost>

apache2: Could not reliably determine the server’s fully qualified domain name

$ sudo service apache2 restart
[sudo] password for jex:
 * Restarting web server apache2
apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1 for ServerName
... waiting apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1 for ServerName

在/etc/apache2/sites-available/default:

ServerName jex.com
<VirtualHost *:80>
    ServerName jex.com
    (...略...)
</VirtualHost>

ServerName宣告為global

在/etc/apache2/httpd.conf加上:

ServerName jex.com

但apache2.conf要記得Include httpd.conf

不存在的virtual host會自動導向(問題發生在將default不存在)

問題: 原本的default被我刪掉了,而加上兩個virtual host

  • w1.test.com
  • w2.test.com

如此一來 w3.test.com 因為不存在,所以會導向w1.test.com

解決方法:

/etc/apache2/sites-available/default 加回來, 即可解決此問題, 當不存在的domain會導向到default

後記 :

  • ServerName變數不可以在每一個virtual host都宣告成global,不然apache會誤判domain
  • 建議ServerName只在default宣告為global變數

apache log

在/etc/apache2/sites-available/default可以找到apache的log存放位置,例如:

ErrorLog ${APACHE_LOG_DIR}/error.log

預設存放在/var/log/apache2/error.log

讓主機上的帳號都有自己的 public 資料夾 - public_html

load /etc/apache2/mods-enabled :

userdir.load

/etc/apache2/mods-enabled/userdir.conf:

 <IfModule mod_userdir.c>
         UserDir public_html
         UserDir disabled root

         <Directory /home/*/public_html>
                 AllowOverride FileInfo AuthConfig Limit Indexes
                 Options MultiViews Indexes FollowSymLinks IncludesNoExec

Indexes、FollowSymLinks

Options -Indexes +FollowSymLinks
  • -Indexes : 不允許顯示檔案目錄結構
  • +FollowSymLinks : 允許使用 .htaccess 檔案 override 預設的設定

特定副檔名忽略同源政策

<Directory> 裡加上

<FilesMatch "\.(ttf|otf|eot|woff|svg)$">
    <IfModule mod_headers.c>
        Header set Access-Control-Allow-Origin "*"
    </IfModule>
</FilesMatch>

使用 Swftools 將 Pdf 轉成圖片

libfreetype6

apt-get install libfreetype6

libgif-dev

apt-get install libgif-dev

libjpeg-dev

apt-get install libjpeg-dev
apt-get upgrade --show-upgraded

zlib1g-dev、freetype

sudo apt-get install zlib1g-dev
sudo apt-get install freetype*

安裝swftools

需先安裝

sudo apt-get install make
sudo apt-get install g++

取得最新版並安裝:

cd /tmp
wget http://www.swftools.org/swftools-2013-04-09-1007.tar.gz
tar -xvf swftools-2013-04-09-1007.tar.gz
cd swftools-2013-04-09-1007
./configure
sudo make
sudo make install

Jodconverter 將word等..轉成pdf

libreoffice

sudo apt-get install libreoffice

fonts dir : /usr/lib/libreoffice/share/fonts/truetype

install font:

example : 文泉驛微米黑 http://wenq.org/wqy2/index.cgi?MicroHei

cd /tmp
wget http://downloads.sourceforge.net/project/wqy/wqy-microhei/0.2.0-beta/wqy-microhei-0.2.0-beta.tar.gz
tar zxvf wqy-microhei-0.2.0-beta.tar.gz
sudo mv wqy-microhei /usr/lib/libreoffice/share/fonts/truetype
rm -rf wqy-microhei*

install jodconverter:

wget https://jodconverter.googlecode.com/files/jodconverter-core-3.0-beta-4-dist.zip
unzip jodconverter-core-3.0-beta-4-dist.zip
mv jodconverter-core-3.0-beta-4 /home/jex/

usege :

java -jar /home/jex/jodconverter-core-3.0-beta-4/lib/jodconverter-core-3.0-beta-4.jar Fonts.docx fonts.pdf

LVM

切割LVM硬碟

1) 檢查有無 PV 在系統上,然後將 /dev/hda6~/dev/hda9 建立成為 PV 格式

sudo pvscan

(會顯示lvm那個partition資料)

2) 建立vg

sudo vgcreate jex /dev/sda5

3) 檢查有無vg

sudo vgscan

4) 顯示vg內容(主要是取得Free PE(example:214564))

sudo vgdisplay

5) 建立lv

sudo lvcreate -l 214564 -n jex-lv jex-vg
Logical volume "jex-lv" created

6) 顯示lv內容(主要是取得LV Path(lv的全名, mkfs要用的))

sudo lvdisplay

7) 檔案系統格式化

sudo mkfs.xfs /dev/jex-vg/jex-lv

8) mount

sudo mount -t xfs /dev/jex-vg/jex-lv /test -o pquota

PHP Switch(0)的問題

當switch帶0進去後會發生一些問題,先來看以下的範例:

function result($num)
{
    switch ($num)
    {
        case $num < 60 :
            echo '<60';
            break;
        case $num > 60 :
            echo '>60';
            break;
        default:
            echo "I don't know!";
            break;
    }
}

echo result(0);    // 結果:>60
echo result(1);    // 結果:<60

為什麼!?

答案就在 官方手冊這一頁Loose comparisons with == 這邊,意思是說當帶0進去時它的判斷會是 0 == (0<60),結果就不會是你預期的,因為0在模糊比對裡也就是代表著false

那什麼情況要用if else什麼情況用switch?

當你要比對的是一個值而不是一個判斷句則使用switch就沒有問題 ex :

case 3 :
    echo '這是3';
    break;

如果要比對的是一個判斷句,代入>0的數值不會出問題,但當代入0時就會出問題,這不是php的bug,只能算是使用上的認知錯誤,因為我們以為它的判斷為0 > 60,但實際上是0 == (0>60),所以比對判斷句就使用if else吧!

    case $num > 60 :
        echo '大於60';
    break;

ref: 夯哥

Linux - Locale

指令

查詢目前的語系設定:

$ locale
locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_MESSAGES to default locale: No such file or directory
locale: Cannot set LC_ALL to default locale: No such file or directory
LANG=zh_TW.UTF-8
LANGUAGE=
LC_CTYPE="zh_TW.UTF-8"
(...略...)
LC_MEASUREMENT="zh_TW.UTF-8"
LC_IDENTIFICATION="zh_TW.UTF-8"
LC_ALL=

查詢已安裝的語系:

$ locale -a
locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_MESSAGES to default locale: No such file or directory
locale: Cannot set LC_COLLATE to default locale: No such file or directory
C
C.UTF-8
POSIX

Error: No such file or directory

locale: Cannot set LC_CTYPE to default locale: No such file or directory

解決方法:

sudo locale-gen --lang zh_TW.UTF-8
Generating locales...
  zh_TW.UTF-8... done
  Generation complete.

再執行locale指令就會正常了

參考來源:

http://120.105.184.250/peiyuli/network-2/shell-scripts.htm

Error: cannot change locale

-bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8)
-bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8)

解決方法, 2選1

在 .bashrc 加上

export LANG=en_US.utf-8
export LC_ALL=en_US.utf-8

或在 /etc/environment 加上

LANG=en_US.utf-8
LC_ALL=en_US.utf-8

Shell Script 基本語法

接收傳入參數

建立test.sh內容:

#!/bin/bash
echo $0
echo $1
echo $2

執行你的shell檔案,並將要傳入的參數加在後面

$ sh test.sh hello 11 22
test.sh         # 檔名
hello           # 第一個參數
11              # 第二個參數

讀寫檔案內容

建立test.sh內容:

cat > qq.php << EOF
this is line 1
this is line 1
EOF

執行後就會產生qq.php檔案了,檔案內容為EOF框起來的內容

寫檔換行 :

echo -e "line1\nline2" | tee -a /tmp/q.txt

append到檔案最後面

tee -a /tmp/t.txt << EOF
line 1
line 2
EOF

讀取檔案,將每行放入陣列

file=1.txt

seq=1
while read line
do
    lines[$seq]=$line
    ((seq++))
done < $file

for ((i=1;i<=${#lines[@]};i++))
do
    echo ${lines[$i]}
done

使用sh執行會有錯誤,但bash執行沒問題

或 :

array_1=($(cat /tmp/t.txt))
echo ${array_1[0]}

使用sh執行會有錯誤,但bash執行沒問題

set

錯誤直接停止, 在最上面加上:

set -e

錯誤不停止,繼續執行

set +e

其他:

  • set -- : 正常看到-後面是option,現在不再是option 而是一個命令參數。如-1 -2 …
  • set -a : 從這邊以後變數自動變成環境變數。
  • set -f : 不要解釋檔名的特殊字元例如wildcard *不再解釋為所有的意思了。
  • set -x : debug shell scripts
  • set -o ignoreeof : 一定要用exit離開shell,本來按Ctrl-D(eof)也可以
  • set -o noclobber : 關掉I/O導向不準overwrite檔案
  • set -o notify : shell結束時報告background job的status
  • set -o noglob : 關掉wildcard字元解釋 如 * ? [ ]
  • set +o : 把-o的反向操作
  • set - : 關掉-v -x -三種選項
  • set -- : 或者 set - 常常用在shell scripts裡面。 set -o 是很常用的例如set -o vi設定shell的操作方式用vi方法, 取回上個命令就是按ESC再按k囉。

ref:

if else

t.sh :

#!/bin/bash

num=$1

if [ "${num}" == "XD" ]; then
    echo "= XD"
elif [ "${num}" != "QD" ]; then
    echo "!= QD"
else
    echo "Nothing"
fi

執行 :

$ bash t.sh XD
= XD
$ bash t.sh something
!= QD
$ bash t.sh QD
Nothing

AND / OR :

[ "${confirm}" == "y" ] && [ "${confirm}" == "Y" ]
[ "${confirm}" == "y" ] || [ "${confirm}" == "Y" ]

switch

case $1 in
    "aa")
        echo "aa"
        ;;
    "cc")
        echo "cc"
        ;;
    "dd")
        echo "dd"
        ;;
    *)
        echo
        echo "  ***\$1 doesn't match!"
        echo
        exit
        ;;
esac

迴圈

for

for var in con1 con2 con3 ...
do
    // do something
done

or

for ((i=0; i<6; i++)); do
  echo $i
  if [ ${i} == 3 ]; then
        continue
        // break
  fi
  echo "done"
done

while

i=1
while [ $i -le 5 ]      // i < 5
do
    ((i++))
    echo $i
done

until

until [ condition ]
do
    程式段落
done

陣列

lines=('line1' 'line2' 'line3')

${#array[@]} 為array的總數 ${array[0]} 索引從0開始, -1為最後一個值

判斷

檔案是否存在

if test -e /etc/network/if-pre-up.d/firewall; then
    echo "/etc/network/if-pre-up.d/firewall exists.."
fi

or

if [ -f /tmp/${filename}.tar.gz ]; then
    echo "exist"
fi

判斷沒有目錄直接離開:

if [ ! -d "/var/www/$dir" ]; then
    echo "/var/www/$dir doesn't exist."
    exit
fi

判斷變數是否存在

if [ -z $1 ]; then
        # 不存在
else
        # 存在
fi

判斷只能輸入數字

echo "$1" | grep -o "^[0-9]*$"
if [ $? -eq 0 ] ;then
    echo "match found"
else
    echo "match not found"
fi

AND 條件

if (( $hour >= 0 )) && (( $hour <= 9 )); then
    # do something
else
    # do something
fi

亂數 10~19 中其中一個數字

shuf -i 10-19 -n 1

執行command結果給變數

random_num=$(shuf -i 10-19 -n 1)

or

count=$(grep "2017-07-07 $hour_str:$min_str" /var/log/php/ci/log-2017-07-07.php | wc -l)

How to run mysql commands through shell script?

mysql -u root test_database << EOF
SELECT * FROM test
EOF

有設定免密碼所以不用加 -p

在shell script執行 發生Bad substitution錯誤

可能發生的原因 ex: shell script 可能使用 ${str:3} (取得str變數第三個字元以後的字串)

因為sh不支援,所以執行時改用bash執行就可以解決了

bash test.sh

執行 ssh 後要怎麼離開

以下是沒有用的:

ssh qq.com
exit

要改成:

ssh qq.com <<ENDHERE
exit
ENDHERE

shell script 回覆 yes

當我要reload supervisor這個套件,但是會有新的一行yes/no,所以要使用這種方法才可以讓shell script幫我填入yes

sudo supervisorctl <<EOF
reload
y | command-that-asks-for-input
EOF

or

yes | sudo sensors-detect
sudo sensors

shell script 下 sudo echo 發生 permission denied

sudo echo "123" >> /etc/fstab

>> 是bash執行,不具root權限,以tee 代替>>

改成:

echo "123" | sudo tee -a /etc/fstab

-a, –append : append to the given FILEs, do not overwrite

match string

只比對jex, 其餘不要

bash :

#!/bin/bash

jex=$(echo $1 | grep -o "jex[0-9]*")
echo ${jex}

-o, –only-matching

取得指定字串長度

str='123456'
echo ${str:3:2}        # 結果為45

read讓使用者輸入變數

sudo fdisk -l
read -p "Please input XFS partition: " xfs_partition
echo $xfs_partition

執行漸進式選項

EX: fdisk partition

(echo o; echo n; echo p; echo 1; echo ; echo; echo w) | sudo fdisk

判斷 command 是否執行成功

some_command
if [ $? -eq 0 ]; then
    echo OK
else
    echo FAIL
fi

The return value is stored in $?. 0 indicates success, others indicates error.

比對檔案是否有符合字串

grep "127.0.1.1" /tmp/hosts > /dev/null 2>&1
if [ $? -eq 0 ]; then
    echo 'match'
fi

function

的第一種寫法,並返回值

function test ()
{
    return 255
}

test
echo $?

結果 : 255 return 範圍是 0~255,超過的話 : 256 為 0 257 為 1 使用 $? 取得 return 的值

第二種寫法,傳值並返回

test2 () {
 echo $1$2
}

string=$(test2 "Hello" " World!")
echo $string

結果 : Hello World! function 並不像 php 一樣順序可以放在執行後,shell script 的 function 必須放在執行前

背景執行不顯示 output

host www.google.com > /dev/null 2>&1

將 command 結果存入陣列

function test_dev_list()
{
    echo "/dev/sda5"
    echo "/dev/sda6"
    echo "/dev/sda7"
    echo "/dev/sda10"
    echo "/dev/sda11"
    echo "/dev/sda14"
}

dev_array=(`echo $(test_dev_list) | cut  -d " " --output-delimiter=" " -f 1-`)
for ((i=0; i<${#dev_array[@]}; i++)); do
    echo $i : ${dev_array[$i]}
done

結果:

0 : /dev/sda5
1 : /dev/sda6
2 : /dev/sda7
3 : /dev/sda10
4 : /dev/sda11
5 : /dev/sda14

減法

a=5
b=3
c=$(($a-$b))
echo $c

結果 : 2

輸出顏色文字及控制背景顏色

printf "\33[0;35;44m"
echo " Menu of available command:"
printf "\33[0m"
printf "\E[0;31;40m"
echo " Menu of available command:"
printf "\E[0m"
printf "\e[0;36;43m"
echo " Menu of available command:"
printf "\e[0m"

\33 = \E = \eprintf 來輸出,而不是一般的 echo

註 :

  • Syntx : \E[樣式;文字顏色;背景顏色m
  • 輸出文字 : \E[0m

樣式:

  • 0 一般樣式
  • 1 粗體
  • 4 加底線
  • 5 灰底
  • 7 文字及背景顏色對調

文字顏色:

30 黑色    90 暗灰
31 紅色    91 亮紅
32 綠色    92 亮綠
33 黃色    93 亮黃
34 藍色    94 亮藍
35 紫色    95 亮紫
36 青藍綠  96 亮青藍綠
37 亮灰    97 白

背景顏色:

40 黑色    100 暗灰
41 紅色    101 亮紅
42 綠色    102 亮綠
43 黃色    103 亮黃
44 藍色    104 亮藍
45 紫色    105 亮紫
46 青藍綠  106 亮青藍綠
47 亮灰    107 白

ref : 參考顏色 http://mark528.pixnet.net/blog/post/7267334-shell-script%3A-%E6%8E%A7%E5%88%B6%E6%96%87%E5%AD%97%E9%A1%8F%E8%89%B2

printf + read

printf "Choose Monkey, are you sure (y/n) ? "
read xfs_choice_confirm

read 後面不需接 -p

補充 if 參數

  • n1 -lt n2 : n1 小於 n2 (less than)
  • -a file : True if file exists.
  • -b file : True if file exists and is a block special file.
  • -c file : True if file exists and is a character special file.
  • -d file : True if file exists and is a directory.
  • -e file : True if file exists.
  • -f file : True if file exists and is a regular file.
  • -g file : True if file exists and is set-group-id.
  • -h file : True if file exists and is a symbolic link.
  • -k file : True if file exists and its ‘‘sticky’’ bit is set.
  • -p file : True if file exists and is a named pipe (FIFO).
  • -r file : True if file exists and is readable.
  • -s file : True if file exists and has a size greater than zero.
  • -t fd : True if file descriptor fd is open and refers to a terminal.
  • -u file : True if file exists and its set-user-id bit is set.
  • -w file : True if file exists and is writable.
  • -x file : True if file exists and is executable.
  • -O file : True if file exists and is owned by the effective user id.
  • -G file : True if file exists and is owned by the effective group id.
  • -L file : True if file exists and is a symbolic link.
  • -S file : True if file exists and is a socket.
  • -N file : True if file exists and has been modified since it was last read.
  • file1 -nt file2 : True if file1 is newer (according to modification date) than file2, or if file1 exists and file2 does not.
  • file1 -ot file2 : True if file1 is older than file2, or if file2 exists and file1 does not.
  • file1 -ef file2 : True if file1 and file2 refer to the same device and inode numbers.

  • [ -b $file ] True if file exists and is block special.

  • [ -c $file ] True if file exists and is character special.
  • [ -d $file ] True if file exists and is a directory.
  • [ -e $file ] True if file exists.
  • [ -f $file ] True if file exists and is a regular file.
  • [ -g $file ] True if file exists and is set-group-id.
  • [ -k $file ] True if file has its “sticky” bit set.
  • [ -L $file ] True if file exists and is a symbolic link.
  • [ -p $file ] True if file exists and is a named pipe.
  • [ -r $file ] True if file exists and is readable.
  • [ -s $file ] True if file exists and has a size greater than zero.
  • [ -S $file ] True if file exists and is a socket.
  • [ -t $fd ] True if fd is opened on a terminal.
  • [ -u $file ] True if file exists and its set-user-id bit is set.
  • [ -w $file ] True if file exists and is writable.
  • [ -x $file ] True if file exists and is executable.
  • [ -O $file ] True if file exists and is owned by the effective user id.
  • [ -G $file ] True if file exists and is owned by the effective group id.

ref: http://www.troubleshooters.com/linux/quickhacks.htm#ShellscriptFileTests

確認是否要執行以下指令

confirm() {
    echo "Press ENTER to continue, or ^C to cancel.";
    read -e ignored
}
confirm

判斷 Ubuntu 版本

LTS="Ubuntu 10.04"
ISSUE=`cat /etc/issue`
if [[ $ISSUE != Ubuntu* ]]; then
    echo "This script is intended for use on Ubuntu, but this system appears
    echo "to be something else. Your results may vary."
    echo
elif [[ `expr match "$ISSUE" "$LTS"` -eq ${#LTS} ]]; then
    echo "what"
fi

是否擁有 sudo 權限

echo "Testing sudo..."
sudo true
if [ $? -ne 0 ]
then
    echo "ERROR: You must be able to sudo to run this script.";
    exit 1;
fi;

動態偵測輸入變數

for TOKEN in $*
do
   echo $TOKEN
done

判斷第幾個參數

if [ $# -lt 1 ]; then

line 10: php: command not found

在使用 shell 的 bash 指令裡使用 php 指令記得要用絕對路徑

可使用 which php 查路徑在哪

指定 USER 執行命令

/usr/bin/sudo -H -u $USER bash -c "php -l qq.php"

每秒執行一次

while true
do
    /bin/sleep 1s
done

Add cron by command line

先印出已存在的 crontab, 然候新增你要的存進去

(crontab -l; echo "0 * * * * your_command") |uniq - | crontab -

Other way

echo "0   0  *  *  * your_command" | sudo tee -a /var/spool/cron/crontabs/jex

Remove cron by command line

先印出已存在的 crontab, 再用 sed 去刪除

crontab -l | sed "/pattern/d" | crontab -

other way

sudo sed -i "/pattern/.*$/d" /var/spool/cron/crontabs/jex

2>&1

2>&1 表示將stderr導向輸出stdout

0: stdin 1: stdout 2: stderr

out.file 2>&1 : out.file是將command的輸出重定向到out.file文件,即輸出內容不打印到屏幕上,而是輸出到out.file文件中。2>&1是將標准出錯重定向到標準輸出,這裡的標準輸出已經重定向到了out.file文件,即將標准出錯也輸出到out.file文件中。 ref: http://www.cnblogs.com/caolisong/archive/2007/04/25/726896.html

截取目錄名

jex@106-185-47-26:~/dockerfile/33-fsx$ echo ${PWD#*-}
fsx

當使用echo 增加資料發生Permission denied

$ sudo echo 'xxx' >> /etc/projects
-bash: /etc/projects: Permission denied

解決方法:

sudo echo 'xxx' | sudo tee -a /etc/projects

multi commands

alias lock='gnome-screensaver; gnome-screensaver-command --lock'

or

lock() {
    gnome-screensaver
    gnome-screensaver-command --lock
}

MySQL Index 解說

為什麼要建立索引:

適當的設定index可以有效的降低資料庫查詢的時間, 但這是以空間爭取時間的做法, 所以建立索引必須先考量到常被查詢的欄位

如果查詢時判斷多個欄位, 建立多組欄位的索引也要考量優先順序, 才能有效降低索引所佔用的資料庫空間

索引的規則:

假設有一組索引是uid+gid+ctime

  • where條件不分先後順序, 假設ctime先再來是uid及gid, 也是符合的
  • 這組索引的重要順序也是依你設定的順序為主, 假設你的索引是uid+ctime, 那麼這組索引只有uid對你有效
  • 如果條件是gid+ctime這組索引是沒有效用的, 因為這組索引是由uid先開始排
  • 換句話說條件為uid+uname的話, 這組索引至少也可以幫你減少搜尋uid的時間n 但uname沒在索引上, 所以mysql還是得一行一行找

使用 EXPLAIN 來檢查執行命令後索引的使用狀況

EXPLAIN SELECT *
FROM  `user_log`
WHERE uid =2
AND gid =2
AND ctime >0

結果:(只列出重要的)

key : uid+gid+ctime  (使用到的索引)
raw : 4              (查詢筆數)

參考來源:夯哥

排序(ORDER BY)

mysql 排序是按照該欄位的編碼, 例如 utf8_unicode_ci 就是按照 UTF8(unicode) 的順序去排 數 > 英 > 日 > 中 > 韓

也可以用 COLLATE 指定排序的編碼

utf8(unicode) table

不會使用到 index 的狀況 (innodb)

如果 where 多個已經有建 index 的欄位, 不會使用到 index

user_id = 'A01' or user_id = 'A02'
IN('A01','A02')

單個才會使用到 index

user_id = 'A01'
IN('A01')

欄位優化

IP 用 int unsigned 存

> echo ip2long('10.0.115.80');
> 167801680

MySQL 常用指令

登入 mysql (使用 -p 密碼登入)

root@jex:/# mysql -u webadmin -p
root@jex:/# mysql -u webadmin --password=00000000 db_name

連入遠端 mysql

mysql -h devHostName -u jex -p
mysql>    #按ENTER是換第二行輸入,
          #不是送出而是繼續輸入,
          #如果不要再輸入了加上分號(;)表示結束
          #quit 或 ctrl + d : 中斷

匯入匯出資料庫

mysqldump 無法將密碼寫在指令上 e.g. --password=00000000, 所要要改用 config 的方式

/home/web-admin/db_backup/my.cnf:

    [mysqldump]
    password=00000000

mysqldump --defaults-file=/home/web-admin/db_backup/my.cnf --opt -u root my_db > my_db_backup.sql

匯出test資料庫, user為jex, 密碼送出才輸入, 匯出到目前的目錄, 檔案名稱為test_backup.sql

mysqldump --opt -u jex -p db_name > db_name_backup.sql

建立 test 使用者及建立 test 資料庫

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*A8950BFBF9522A31DA227E7E1E751E2DC0691964' WITH GRANT OPTION;
mysql> create database db_name

匯入 test 資料庫 (不論是匯入 database 或是 table, 寫法都一樣)

mysql -u root -p db_name < db_name_backup.sql

匯出單筆 user 資料

mysqldump -u root my_db user -w "email='jex_lin@gmail.com'"

匯出 test DB, 無資料(但仍保留AUTO_INCREMENT)

mysqldump --opt -u jex -p --no-data test > test_backup.sql

如果使用 innoDB 可能會有 lock 情況造成無法匯出, 加上 --lock-tables=false

匯出 test DB 裡的 qq table, 無資料(但仍保留AUTO_INCREMENT)

mysqldump --opt -u jex -p --no-data test qq > test_qq_backup.sql

匯出 test DB 的 groups 資料表 uid=6 的資料

mysqldump -u root test groups -w "uid=6" > group_test.sql

匯出 test DB, 無資料, AUTO_INCREMENT重置

mysqldump -u jex -p --no-data --skip-add-drop-table test | sed 's/AUTO_INCREMENT=[0-9]*\b//' > test_backup.sql

匯出遠端 mysql 資料

mysqldump -h sqlserver.com -u me -p123 me_test user -w "name='jex'" > qq

Host : sqlserver.com
Account : me
Password : 123
DB : me_test
Table : user
WHERE 條件 : name='jex'

管理 MySQL 會用到的語法

看目前連到 MySQL 的 process

SHOW FULL PROCESSLIST;
+--------+----------+-------------------+-------+---------+------+----------+-----------------------+
| Id     | User     | Host              | db    | Command | Time | State    | Info                  |
+--------+----------+-------------------+-------+---------+------+----------+-----------------------+
| 530566 | api      | 10.0.21.49:43537  | my-log | Async commit | 1 | cleaned up | INSERT INTO `device_log` (`model`, `did`, `uid`, `mac`, `category`, `action`, `ip`, `log_date`, `occurred_at`, `created_at`, `timestamp_milli`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Show schema threads

SELECT * FROM performance_schema.threads;

其他系統指令

SHOW ENGINE INNODB STATUS;
SHOW GLOBAL STATUS;

Show running queries

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep';

Shows all queries running for 5 seconds or more:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' AND TIME >= 5;

Show all running UPDATEs:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' AND INFO LIKE '%UPDATE %';

目前連接 mysql 的數量

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

最多可以同時幾個連線 (注意,如果超過這個數量,mysql 就會回 Too many connections 錯誤),如果使用 AWS RDS MySQL 要參考它不同 size 支援的 max_connections 量

SHOW VARIABLES LIKE 'max_connections';

MySQL version

SHOW VARIABLES LIKE "%version%";
或
select version();

binlog 是否開啟

SHOW VARIABLES LIKE 'log_bin';

slow query 是否開啟

SHOW VARIABLES LIKE '%slow%';

將執行過的 Query 寫到 log 檔

SET GLOBAL general_log = 'ON';

查詢指令使用index情況,在開頭加上EXPLAIN

XPLAIN SELECT *
FROM  `user_log`
WHERE uid =2

顯示系統狀態(詳細)

show status;

顯示系統狀態(簡單)

status;

查看自已的權限

mysql> show grants;

查看某個 user 的權限

show grants for jex@'localhost';

列出 mysql 的 user

mysql> select user, host from mysql.user;

查看權限

select * from mysql.user where user='jex';

查看 mysql 帳戶密碼及 host

select host, user, password from mysql.user;

mysql privileges

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, EVENT, TRIGGER, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE

查看 mysql 帳號

desc mysql.user;

新增 User

use mysql;
INSERT INTO user(host,user,password) VALUES('127.0.0.1','jex',password('qwer7890'));

限定權限

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON testDB.* TO jex@localhost IDENTIFIED BY 'qwer7890';
FLUSH PRIVILEGES;

所有權限

GRANT ALL PRIVILEGES ON *.* TO jex@localhost IDENTIFIED BY 'qwer7890' WITH GRANT OPTION;
等於
GRANT ALL PRIVILEGES ON *.* TO jex@localhost IDENTIFIED BY PASSWORD '*9B25933BE82583D0F86E4AB11A340DE6A3611ACD' WITH GRANT OPTION;

Show cache

show variables like '%cache%';

直接操作時會用到的語法

顯示資料庫清單

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| exam               |
| mysql              |
| package            |
| performance_schema |
| phpmyadmin         |
| test               |
+--------------------+
7 rows in set (0.02 sec)

選擇資料庫

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

顯示資料表清單

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

顯示table的欄位資訊

mysql> show columns from test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40) | NO   |     | NULL    |                |
| hobby | varchar(40) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

或者:

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40) | NO   |     | NULL    |                |
| hobby | varchar(40) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

顯示資料表的 TABLE Schema

mysql> show CREATE table test;
+-------+------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                   |
+-------+------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
          `name` varchar(80) DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

顯示 user 開頭的 table 列表

SHOW tables like 'user%';

Create database

CREATE DATABASE db_name

Use database

USE db_name

Drop database

DROP DATABASE db_name

DROP TABLE IF EXISTS `info`;

Create table

CREATE TABLE table_name (no char(4), name char(10));

CREATE TABLE `info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `version` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `url` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `version` (`model`,`version`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Drop table

DROP TABLE table_name;

Rename table name

RENAME TABLE table_name TO tt;

Display database list

SHOW DATABASES;

Display table list

SHOW TABLES;

Display table detail

DESC table_name;

Alter column

ALTER TABLE qq_tab CHANGE qq_col qq_col varchar(80) NOT NULL;

Add column

ALTER TABLE tt ADD phone varchar(40);

Primary key

ALTER TABLE gearman_queue ADD COLUMN `id` INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT;

Replace

UPDATE table_name SET field=replace(field, "android", "iphone");
UPDATE `supe_spacenews` SET `message` = REPLACE(`message`,'要替換的文字','替換後的文字') WHERE `message` LIKE '%要替換的文字%'

將欄位改成 allow null

ALTER TABLE my_table MODIFY url varchar(100);

Show index

SHOW INDEX FROM my_table;

Foreign key

SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;

顯示 Table 的 Index

SHOW INDEX FROM users;

刪除資料表內的所有資料,並且重置AUTO_INCREMENT

TRUNCATE TableName

LIKE IN 語法怎麼辦? 使用 REGEXP

SELECT * FROM students WHERE name REGEXP 'bob|jex|joyce';

設定 AUTO_INCREMENT

ALTER TABLE tbl AUTO_INCREMENT = 5;

更新與 users.uid 的關聯 parent_id

UPDATE parents set parent_id = (SELECT uid from users where users.username = parents.parent) where parents.parent != ''

程式裡會用到的語法

INSERT

INSERT INTO test2 (name, passwd) VALUES ('jex', '123');

INSERT IF NOT EXISTS

INSERT INTO promote_grab (m_id)
SELECT (2)
FROM DUAL
WHERE NOT EXISTS(SELECT 1 FROM promote_grab WHERE m_id = 2)

插入 m_id = 2 到 promote_grab, 如果 promote_grab 不存在 m_id =2 :

INSERT IGNORE (if exists)

INSERT IGNORE INTO books (id, title, author, year_published) VALUES (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960)

插入一樣的 index 如果已存在就會 ignore 不會噴 error

INSERT or UPDATE IF EXISTS

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name="A", age=19

INSERT Table to another one : 將 user 資料表的資料都 insert 到 user2 資料表 :

INSERT INTO `user2` SELECT * FROM `user`

Insert or Update

INSERT INTO student (id, name) VALUES('1', 'Jex') ON DUPLICATE KEY UPDATE name='Bob'

UPDATE

UPDATE question_options SET question_id = 20, optional_item = 5 WHERE id = 64;

UPDATE with EXISTS

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT *
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

DELETE

DELETE FROM videos WHERE id=1;

DELETE with EXISTS

DELETE FROM suppliers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE suppliers.supplier_id = orders.supplier_id);

將 time 欄位加30天

DATE_ADD(time, INTERVAL 30 DAY) AS after_time

select 特定時間

select * from device where date_format(update_time, '%Y-%m-%d') = '2014-10-23';

WHERE json 裡的欄位

SELECT * FROM `user` WHERE profile->"$.email" != ""                         // email 是欄位名稱
SELECT * FROM `user` WHERE profile->'$."example+11@example.com"' != ""      // 如果欄位名稱有符號要用 雙引號包起來

IFNULL(x, y): 如果 x 欄位的值是 NULl 就回傳 x, 否則是 y

GROUP BY IFNULL(ann_id, id)

FORCE INDEX 強制指定某個 index (寫在 from 前)

FORCE INDEX (uid+age+created_at)

OFFSET

// return only 10 records, start on record 16 (OFFSET 15)
SELECT * FROM Orders LIMIT 10 OFFSET 15
SELECT * FROM Orders LIMIT 15, 10

UNION 將兩個不同表但欄位類似的 Query 合成一個結果

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

如果只取一個欄位的話, UNION 預設會幫你去除重覆的, 如果要顯示重覆的話要用 UNION ALL

UNION + Unique 每個欄位

SELECT * FROM (
    SELECT City, Code FROM Customers
    UNION
    SELECT City, Code FROM Suppliers
    ORDER BY City;
) C
GROUP BY City

DISTINCT 沒有用, 要用 GROUP BY

JOIN

gui.JPG

A1

1 aaa
2 bbb

A2

1 bbb
2 ccc

Left join SELECT * FROM `A1` LEFT JOIN A2 ON A1.did = A2.did

id did id did
2 bbb 1 bbb
1 aaa NULL NULL

Right join SELECT * FROM `A1` RIGHT JOIN A2 ON A1.did = A2.did

id did id did
2 bbb 1 bbb
NULL NULL 2 ccc

Inner join SELECT * FROM `A1` INNER JOIN A2 ON A1.did = A2.did

id did id did
2 bbb 1 bbb

子查詢

限制

1) 不可以傳回兩個欄位以上

2) 不可以傳回兩筆以上的紀錄

SELECT *
FROM users
WHERE uid = (SELECT parent_id FROM parents WHERE student_id=1109 LIMIT 1)

其他

3-tier Category

+--------+---------------+-----------+
| cat_id | name          | parent_id |
+--------+---------------+-----------+
|      1 | Electronics   |         0 |
|      2 | Appliances    |         0 |
|      3 | Cell phones   |         1 |
|      4 | Computers     |         1 |
|      5 | Tablets       |         1 |
|      6 | Smartphones   |         3 |
|      7 | Tablet Phones |         3 |
+--------+---------------+-----------+

mysql> SELECT
    -> a.name AS main_category,
    -> b.name AS second_level_category,
    -> c.name AS thrid_level_category
    ->  FROM categories AS a
    -> LEFT JOIN categories AS b ON (a.cat_id=b.parent_id)
    -> LEFT JOIN categories AS c ON (b.cat_id=c.parent_id)
    -> WHERE a.parent_id=0;
+---------------+-----------------------+----------------------+
| main_category | second_level_category | thrid_level_category |
+---------------+-----------------------+----------------------+
| Electronics   | Cell phones           | Smartphones          |
| Electronics   | Cell phones           | Tablet Phones        |
| Electronics   | Computers             | NULL                 |
| Electronics   | Tablets               | NULL                 |
| Appliances    | NULL                  | NULL                 |
+---------------+-----------------------+----------------------+

Rails ORM 版

Category.from('categories AS a').where('a.parent_id = ?', 0).
         joins('LEFT JOIN categories AS b ON (a.id = b.parent_id)').
         joins('LEFT JOIN categories AS c ON (b.id = c.parent_id)').
         select('a.name AS main, b.name AS second, c.name AS third')

ref : http://stackoverflow.com/questions/21277663/mysql-normalization-with-category-with-1st-tier-sub-category-and-2nd-tier-sub-ca

LIKE 語法關鍵字 _ %

  • % : 0 個或以上的任意字元
  • _ : 只能有一個字元

How to prevent sql injection

  • 限制 db user 的權限
  • 使用程式提供的工具去過濾,也盡量避免以下字元進入到 query : ' " \ & * ;
  • 使用 Prepare 取代 query 的變數
  • 使用一些 SQL 的檢查工具 e.g. sqlmap, SQLninja
  • 避免將 SQL 錯誤發生時的資訊印出

支援儲存 unicode character(emoji,特殊字元)

utf8 vs utf8mb4

  • utf8 is a variable-length encoding. In the case of UTF-8, this means that storing one code point requires one to four bytes. However, MySQL’s encoding called “utf8” only stores a maximum of three bytes per code point.
  • utf8mb4 character set uses a maximum of four bytes per character

1) 將欄位改成 utfmb4_unicode_ci

2) 確認連線時的 Server charset, 如果預設是 UTF-8 Unicode (utf8), 那麼與 mysql 建立連線時的 dsn 就要加上 &charset=utf8mb4

SHOW VARIABLES WHERE variable_name LIKE 'character%' OR variable_name LIKE 'collation%'
  • The server character set and collation are the values of the character_set_server and collation_server system variables.
  • The character set and collation of the default database are the values of the character_set_database and collation_database system variables.
  • The server takes the character_set_client system variable to be the character set in which statements are sent by the client.

如果連線的 client connection 沒有使用 utf8mb4, 在撈含有特殊字元的資料不會 error, 但字元會變成 ?