[SQL] MySQL to PostgreSQL 好痛轉移,SQL語法差異

換資料庫真的好機掰,會有一堆未知問題,見招拆招,紀錄於下

LIMIT OFFSET

/* MySQL */
SELECT * FROM abc WHERE status=1 ORDER BY create_time DESC LIMIT 0 , 10
/* PostgreSQL */
SELECT * FROM abc WHERE status=1 ORDER BY create_time DESC LIMIT 10 OFFSET 0

# UPDATE 不能加 ORDER BY and LIMIT
/* MySQL */
UPDATE abc SET aaa=1 WHERE id<=100 ORDER BY status DESC LIMIT 10

/* PostgreSQL */
/* 若有需求要用子句解決 */
UPDATE abc main SET aaa=1
FROM
 ( SELECT id FROM page_builder WHERE id<=100 ORDER BY status DESC LIMIT 10 ) sub
WHERE main.id = sub.id

PHP

//mysql 取新增的id
mysql_insert_id() mysqli_insert_id();
//PostgreSQL

$re = pg_query($conn, "INSERT INTO foo (bar) values (123);");
$re
= pg_query("SELECT lastval();");
$row
= pg_fetch_row($re);
$insert_id
= $row
[0];

[……]

閱讀更多

MySQL 常用資料型態

MySQL 資料庫的資料欄位型態可分為文字類型、數值類型、日期時間類型,與特殊類型,下面列出常用的欄位型態:
文字型態
適用於儲存文字資料的欄位型態,其中 VARCHAR 、 CHAR 必須指定文字長度 實際儲存的資料超過設定的文字長度,雖可儲存但超過的部份將被截斷。例如儲存文字「 How are you ? 」到文字長度設定為 10 的欄位,將只剩下「 How are yo 」,若設定過度的文字長度則會佔用儲存空間,建議視實際儲存的資料需求設定文字型態。
資料型態儲存位元說明VARCHAR (M)

1~255

非 固 定 長 度 字 元 的 資 料 型 態
例 如 儲 存 通 訊 地 址 。
CHAR (M)

1~255

固 定 長 度 字 元 的 資 料 型 態
例 如 儲 存 身 份 證 字 號 。
TINYTEXT

255

適 用 於 儲 存 255 字 元 以 內 的 資 料TEXT

65535

適 用 於 儲 存 較 多 字 元 的 資 料
例 如 訪 客 留 言 板 的 內 容
MEDIUMTEXT

1677215

適 用 於 儲 存 大 容 量 文 字 的 資 料LONGTEXT

4294967295

適 用 於 儲 存 超 大 容 量 文 字 的 資 料
數值型態
適於於儲存數字資料的欄位型態,例如會員編號、商品價格、學生成績等,設定為 signed 時資料範圍可以是負值,設定為 unsigned 時資料範圍僅能儲存正值。必須注意數值型態的儲存範圍,例如商品價格若為 5000 元,儲存於 TINYINT 數值型態的欄位,查詢時將變成 127 元 ( signed ) 或 255 元 ( unsigned ),造成顯示或計算錯誤的問題,若設定較大的資料範圍則會佔用儲存空間,同樣建議視實際儲存的數值上限設定數值型態。
資料型態位元資料範圍TINYINT

1

signed: -128 ~ 127
unsigned: 0 ~ 255
SMALLINT

2

signed: -32768 ~ 32767
unsigned: 0 ~ 65535
MEDIUMINT

3

signed: -8388608 ~ 8388607
unsigned: 0 ~ 16777215
INT

4

signed: -2147483648 ~ 2147483647
unsigned: 0 ~ 4294967295
BIGINT

8

signed: -9223372036854775808 ~ 9223372036854775807
unsigned: 0 ~ 1844674407370951615
日期時間型態
資料型態儲存位元資料範圍DATE3 bytes1000-01-01 ~ 9999-12-31TIME3 bytes-838:59:59 ~ 838:59:59DATETIME8 bytes1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

有關 MySQL 資料型態的詳細介紹可參考官方網站的說明:
http://dev.mysql.com/doc/refman/5.0/en/data-types.html
文章出處
http://www.cg.com.tw/php/htm/Dreamweaver_005.asp

[……]

閱讀更多

SQL GROUP BY SUBSTR 用分割字串來群組資料

有時候我們會用一定的規則來訂義產品編號
例如:

2014(年)+001(款號)+01(規格) = 
201400101(產品編號)
所以在列表中,其實我們是要顯示款列表,但不同規格其實還是同一款,大同小異,
此時我們會希望用款號來把相同款式的商品群組起來,秀一筆就好,GROUP BY可以搭配SUBSTR()來實現這效果,實際實作方式如下

product_id ( 產品編號欄 )
201400101
201400102
201400201
201400202
201400301
201400302
GROUP BY SUBSTR(product_id,5,3)

取出之產品編號 
201400101
201400201

201400301

參考
http://www.1keydata.com/tw/sql/sql-substring.html

[……]

閱讀更多

MySQL 自訂ORDER BY捱序或 ORDER BY 忽略

1. 只按日期排序,忽略年份
> select date, description from table_name order by month(date),dayofmonth(date);
注意:單純使用dayofyear來排序會導致錯誤,如2-29與非閏年的3-1日同等級

2. 排序點分式IP
> select ip from table_name order by inet_aton(ip);
或者在設計表時就使用int unsigned 來表示ip

3. 將某列中特定值排在最前
例如想把表中lulu的名字排在最前顯示,其他按字母排序
> select name from table_name order by if(name=’lulu’,0,1),name ;
也可以把if的條件根據需要換成相應的語句。

4. 將某列內容按照使用者自訂的順序排序
例如想把表中的名字按lulu,xixi,baba,mama的非常規順序排序輸出:
> select name from table_name order by field(name,’lulu’,’xixi’,’baba’,’mama’);

5. 對枚舉類型排序
枚舉類型預設為數字排序,如果希望以字串形式排序。
> select name from table_name order by cast(name as char);
如果希望更改enum的排序預設順序,可以alter table
alter table table_name modify name enum(‘lulu’,’xixi’,’mama’,’baba’);

6. csv類型的字串的某字串排序
例如某列m_str內容是形如abc-321-mno-jkl的形式,希望對第二列進行排序
> select m_str from table_name
order by substring_index(substring_index(m_str,’-‘,2),’-‘,-1);

[……]

閱讀更多

mysql的sql檔太大無法匯入又無法更改php.ini 解決方案 BigDump

在MySQL中匯入資料庫的方式有很多種,其中一般比較不熟MySQL指令、或沒有伺服器管理權限的使用者,最常用的方式就是使用phpMyAdmin管理程式的匯入介面來上傳、匯入。不過如果你的「.sql」檔案超大的話,則很容易遇到伺服器的執行時間或檔案大小的限制,結果就是太大的「.sql」檔無法順利上傳、匯入到資料庫中。如果遇到這類情況,而你又沒有權限可以修改「php.ini」檔案中的設定的話,那該怎麼辦呢?

介紹一個相當實用的「BigDump」小工具,他可以幫我們將超大的「.sql」資料檔直接匯入到MySQL資料庫中,儘管你沒有權限修改「php.ini」組態檔中的「upload_max_filesize」或「max_input_time」…等設定值,也可以直接用「BigDump」這支小程式來完成超大資料庫的匯入工作。

重灌狂人這篇文章介紹的很詳細,也有詳細的
若有需要的冰友呀~請瀏覽下方網址

***

轉自:重灌狂人
標題:如何匯入大於2MB以上的MySQL資料庫? (BigDump)

網址:http://briian.com/?p=5102

[……]

閱讀更多