標籤: mysql

[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 ~ 255SMALLINT
2
signed: -32768 ~ 32767
              unsigned: 0 ~ 65535MEDIUMINT
3
signed: -8388608 ~ 8388607
              unsigned: 0 ~ 16777215INT
4
signed: -2147483648 ~ 2147483647
              unsigned: 0 ~ 4294967295BIGINT
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
[……]
閱讀更多