[MySQL]MySQL的replace用法，取代字串功能

1.replace into

replace into table (id,name) values(1,'MYSQL'),(2,'GG')
此語法的作用是將table中插入兩條記錄。
如果主鍵id為1或2不存在則做新增動作，如果已存在則做替換動作。


2.replace(object,search,replace)

把object中出现search的全部替換為replace

select replace('123QWERTYEEE','E','1')--->123QW1RTY111

例：把表table中的name字段中的123替换为456
update table set name=replace(name,'123','456')

參考網站
http://dev.mysql.com/doc/refman/5.0/en/replace.html

[MySQL]多行查詢結果欄位資料合併成單一行欄位資料 語法GROUP_CONCAT()

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [[SEPARATOR  str_val])
*SEPARATOR是要用什麼為分割字
參考資料

例：
資料建立一個訂單明細的資料表。
CREATE TABLE products_table (
  order_id bigint(20) NOT NULL default '0' COMMENT '關連訂單id',
  product_name varchar(255) NOT NULL default '' COMMENT '產品名稱',
  price bigint(20) NOT NULL default '0' COMMENT '價格'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO products_table(order_id,product_name,price)
VALUES
        (1,"商品1",10),
        (2,"商品1",10),
        (3,"商品1",10),
        (1,"商品2",20),
        (1,"商品3",30),
        (2,"商品4",40),
        (2,"商品5",50),
        (3,"商品6",60);

查詢語法1：(使用預設的)

SELECT p.order_id,
       GROUP_CONCAT(p.product_name ) as '訂單產品明細'
FROM products_table AS p
GROUP BY p.order_id;
結果：

查詢語法2:(可修改設定，排序、分割子元。)

SELECT  p.order_id,
        GROUP_CONCAT(DISTINCT p.product_name
        ORDER BY p.product_name DESC SEPARATOR ' ') as '訂單產品明細'
FROM products_table AS p
GROUP BY p.order_id;
結果：

[MySQL]字串合併、字串連接 語法CONCAT()

CONCAT(str1,str2,...)
用法例：
SELECT CONCAT('M','y','S','Q', 'L');
結果：MySQL

用法例：
SELECT CONCAT(user.username,user.password ) FROM user

[MySQL]字串長度與字串切割CHARACTER_LENGTH()、CHAR_LENGTH()、LENGTH()

字串長度測量函式分別是CHARACTER_LENGTH()、CHAR_LENGTH()、LENGTH()。
CHARACTER_LENGTH()、CHAR_LENGTH()是相同的，以UTF8來計算，中英文都可以計算。
LENGTH()也可以計算中英文，但對中文的計算會因編碼不同而計算不同，分別是UTF8一字三位，BIG5一字二位。


字串切割函式分別是SUBSTRING_INDEX(culomnName, exp, number)、SUBSTR(culomnName, startIndex, length)。
SUBSTRING_INDEX(culomnName, exp, number)︰
exp是你想開始切割的辨別字元。
number則是以正負數兩種區分，當輸入正數則顯示辨別字左方所有字元，當輸入負數則顯示右方所有字元，如果輸入0會無法辨別。

SUBSTR(culomnName, startIndex, length)︰
startIndex是欲切割起始的index，開頭第一位是1，如果輸入0會無法辨別。
length是自起始index想要切的長度，一字一位元包含空白字元也要算。

以下將由範例一一示範解說︰

使用SUBSTRING_INDEX(culomnName, exp, number)
SELECT  username, SUBSTRING_INDEX(username, '.', 1)
FROM     user


結果︰辨別字元是'.'，輸入正數所以只出現左邊的所有字元

使用 SUBSTRING_INDEX(culomnName, exp, number) 但輸入負數
SELECT  username, SUBSTRING_INDEX(username, '.', -1)
FROM     user


結果︰只出現右邊所有字元

使用CHARACTER_LENGTH()，並且限定只有字元長度8的才能出現
SELECT  username, CHARACTER_LENGTH(username)
FROM     user
WHERE   CHARACTER_LENGTH(username) = 8

結果︰只有八位元的才能出現，值得注意的是此函式會把中文字也只算一個字元。

使用CHAR_LENGTH()，順便練習一下字串切割，一樣限定字元長度8才能出現

SELECT  username, SUBSTRING_INDEX(username, ' ', -1) , CHAR_LENGTH(username)
FROM     user
WHERE   CHAR_LENGTH(username) = 8

結果︰本次辨別字元改成空字元，從空字元開始往右全部字元皆顯示，搭配字元計算。

使用LENGTH()，因為資料是中英文夾雜所以條件使用CHARACTER_LENGTH()
SELECT  username, LENGTH(username), CHARACTER_LENGTH(username)
FROM     user
WHERE  CHARACTER_LENGTH(username) = 8

結果︰條件使用CHARACTER_LENGTH()所以中文也算只有8位，但LENGTH()計算中文的時候則多出了6位，因為這份資料是用UTF8編碼，所以中文一字三位元。

使用SUBSTR(),起始以第1位開始切取4位元，並計算是否真的這個切割出來的字串有4位
SELECT username, SUBSTR(username, 1, 4), CHAR_LENGTH(SUBSTR(username, 1, 4))
FROM    user

結果︰

總結︰以目前台灣人的資料而言使用CHARACTER_LENGTH()、CHAR_LENGTH()，會比較安全，但也要看資料庫的編碼而定，而在字串切割部份，兩種各有特色則是視需求選用了。

其它文章

[MySQL]把字串轉數字排序，使用CAST()函式

(把字串轉數字排序)欄位varchar型態，用數字型態排序，使用CAST()函式


drop table table_a;
CREATE TABLE table_a (
id bigint(10) NOT NULL AUTO_INCREMENT,
number varchar(255)DEFAULT NULL,
PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into table_a(number) VALUES(1),(10),(5),(3),(2),(11),(14),(12),(13);


SELECT * FROM table_a AS a ORDER BY CAST(a.number AS UNSIGNED);

SELECT * FROM table_a AS a ORDER BY CAST(a.number AS decimal(10,0))

其它文章

[MySQL]函式ELT()從傳入的順位數值取出對應欄位的字串

ELT()從傳入的順位數值取出對應欄位的字串
ELT(N,str1,str2,str3,...)
N為傳入的順位數值
str1,str2,str3,...傳入的字串

若N=1，則回傳值為str1，
若N=2，則回傳值為str2，
以此類推。
若N小於1或大於參數的數目，
則回傳值為NULL。
ELT()是FIELD()的補數。

SELECT ELT(1,'live', 'love', 'like','llee');
'live'

SELECT ELT(4, 'live', 'love', 'like','llee');
'llee'

其它文章

[MySql]好用的比對函式FIELD() 回傳字串在列表中的順位

FIELD() 回傳字串在列表中的順位


FIELD(str,str1,str2,str3,...)
str傳入的字串
str1,str2是指傳入列表的字串

回傳值為字串str在str1, str2, str3,……列表中的順位。
在找不到str的情況下，回傳值為0。
如果所有對於FIELD()的參數均為字串，
則所有參數均按照字串進行比較。
如果所有的參數均為數字，則按照數字進行比較。
否則，參數按照double格式進行比較。
如果str為NULL，則回傳值為0，
原因是NULL不能同任何值進行同等比較。
FIELD()是ELT()的補數。
SELECT FIELD('live', 'live', 'love', 'like','llee');
1

SELECT FIELD('love', 'live', 'love', 'like','llee');
2

其它文章

[MySQL]（字串數字轉換整數數字）使用conv函数

CONV （ N ，from_base，to_base）

轉換不同數量的進制之間的數字 。

返回一個數字的字符串表示 N，

從基本轉換 from_base(原進制) to_base(要轉換的進制)。

如果任何參數是 NULL，將 返回 NULL 。

 參數 n 被解釋為一個整數，

但可能被指定為一個整數或字符串。

如果to_base 是一個負數，

 N 被視為有符號數。

否則， N 被視為無符號。

CONV（）與64位精度。

例：

SELECT CONV("30",10,10)

回傳是30 而不是"30"

SELECT CONV（'A'，16,2)

回傳:1010

SELECT CONV（'6 E“，18,8）;

 

回傳:172

SELECT CONV（-17,10，-18）;

回傳:- H

SELECT CONV（10 + '10'+ '10'0 XA，10,10）;

回傳:40

參考資料:http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

\

其它文章