---
# System prepended metadata

title: MySQL-Func
tags: [' SQL', MySQL]

---

---
tags: MySQL, SQL
---

# MySQL-Func

[TOC]

* * *

## 字元函式：

### 連接字串

-   CONCAT(A, B, C) COLUMNS or 任意字元
-   CONCAT_WS('str', A, B, C) str:分割字元
-   SPACE(n) n 空白字元數量
-   IFNULL(COLUMN, 'str') COLUMN 如果是NULL 輸出 str

### 插入字串

-   INSERT('str', n, m, 's')
    將'str' 的 n 字元 ~ n+m 字元 由 's' 字串取代

### 替換字串

-   REPLACE('str', 'r', 's')
    在'str'字串中的字串 'r' 用 's' 取代  --> 'sts'
    沒有 'r' 則不處理

### 反轉字串 也可以放字串 COL

-   REVRESE('COLUMN') - > 'NMULOC'

### 重複字串

-   REPEAT('STR', 'NUM') NUM 可用數字COL

### 大小寫轉換：

-   大寫UPPER() UCASE()
-   小寫LOWER() LCASE()

### 輸出'字串' 用單引號框住'STR'

-   QUOTE(COLUMN)

### 修正(刪除)空白字元

| /   | func    |
| --- | ------- |
| 右   | RTRUM() |
| 左   | LTRIM() |
| 雙邊  | TRIM()  |

### 修正(刪除)其他字元

TRIM('str', COLUMN) ,要修正的字元'str'
strXXXXXXstr -> XXXXXX

### 補位(str)  n:字元數

-   RPAD(COLUMN, n, 'str') -> COLUMN....
-   LPAD(COLUMN, n, 'str') -> ....COLUMN

### 擷取SUBSTRING()

| func                           | /                                       |
| ------------------------------ | --------------------------------------- |
| RIGHT(COLUMN, n, '.')          | n 字元數 "COL"UMN                          |
| LEFT(COLUMN, n, '.')           | n 字元數 COL"UMN"                          |
| MID(COLUMN, n, m)              | CO"LUM"N  n=2 m=3 (n:起始字元index m:擷取字元數) |
| SUBSTRING(COLUMN, n, m)        | 同MID()                                  |
| SUBSTRING(COLUMN FROM n FOR m) | n ~ m個字元                                 |
| SUBSTRING(COLUMN, -m)          | 倒數m 個字元                                 |
| LOCATE('STR', COLUMN)          | COLUMN 中的STR INDEX 第幾字元                 |
| POSITION('STR' IN COLUMN)      | COLUMN 中的STR INDEX 第幾字元                 |

SUBSTRING_INDEX(COLUNM, 'STR', n) 第n STR 之前的字元
	STR: |
	COL: A|B|C
	n:    1 2

### 字元數

| func                       | /      |
| -------------------------- | ------ |
| LENGTH(COLUMN) COLUMN      | 字串字元數  |
| CHAR_LENGTH(COLUMN) COLUMN | 字串字元數  |
| CHARACTER_LENGTH(COLUMN)   | 字串字元數  |
| BIT_LENGTH(COLUMN)         | 字串佔位元數 |

### 時間資料格式：

| func      | /                    |
| --------- | -------------------- |
| DATE      | yyyy-mm-dd           |
| TIME      | hhh:mm:ss            |
| DATETIME  | yyyy-mm-dd hhh:mm:ss |
| TIMESTAMP | yyyy-mm-dd hhh:mm:ss |
| YEAR      | yyyy                 |

* * *

### 時間格式擷取函式：

| func     | /            |
| -------- | ------------ |
| DATE()   | 擷取yyyy-mm-dd |
| TIME()   | 擷取hhh:mm:ss  |
| YEAR()   | 擷取yyyy       |
| MONTH()  | 擷取mm         |
| DAY()    | 擷取dd         |
| HOUR()   | 擷取hhh        |
| MINUTE() | 擷取mm         |
| SECOND() | 擷取ss         |

* * *

### 使用EXTRACT()函式擷取：

EXTRACT(interval FROM DATETIME_COLUMN)
interval              Format for given values

| func               |     format    |
| ------------------ | :-----------: |
| DAY                |       dd      |
| DAY_HOUR           |    ‘dd hh'    |
| DAY_MICROSECOND    |    ‘dd.nn’    |
| DAY_MINUTE         |   ‘dd hh:mm’  |
| DAY_SECOND         | ‘dd hh:mm:ss’ |
| HOUR               |       hh      |
| HOUR_MICROSECOND   |    ‘hh.nn’    |
| HOUR_MINUTE        |    ‘hh:mm’    |
| HOUR_SECOND        |   ‘hh:mm:ss’  |
| MICROSECOND        |       nn      |
| MINUTE             |       mm      |
| MINUTE_MICROSECOND |    ‘mm.nn’    |
| MINUTE_SECOND      |    ‘mm:ss’    |
| MONTH              |       mm      |
| QUARTER            |       qq      |
| SECOND             |       ss      |
| SECOND_MICROSECOND |    'ss.nn’    |
| WEEK               |       ww      |
| YEAR               |       yy      |
| YEAR_MONTH         |    ‘yy-mm'    |

* * *

### 日期時間格式化函式：

DATE_FORMAT(COLUMN, '%code') 也可以處理TIME_FORMAT()
TIME_FORMAT(COLUMN, '%code') 其實可以用不到！

| %     | func                                                                                                                   | unit                    |
| ----- | ---------------------------------------------------------------------------------------------------------------------- | ----------------------- |
| Code  | Description                                                                                                            | Results                 |
| %a    | Abbreviated weekday name                                                                                               | (Sun...Sat)             |
| %b    | Abbreviated month name                                                                                                 | (Jan...Dec)             |
| %c    | Month (numeric)                                                                                                        | (1...12)                |
| %d    | Day of the month (numeric)                                                                                             | (00...31)               |
| %D    | Day of the month with English suffix (1st, 2nd, 3rd, etc.)%e Day of the month                                          | (numeric) (0...31)      |
| %f    | Microseconds (numeric)                                                                                                 | (000000...999999)       |
| %h,%I | Hour 12hr                                                                                                              | (01...12)               |
| %H    | Hour 24hr                                                                                                              | (00...23)               |
| %i    | Minutes (numeric)                                                                                                      | (00...59)               |
| %j    | Day of the year                                                                                                        | (001...366)             |
| %k    | Hour                                                                                                                   | (0...23)                |
| %l    | Hour                                                                                                                   | (1...12)                |
| %m    | Month (numeric)                                                                                                        | (01...12)               |
| %M    | Month name                                                                                                             | (January...December)    |
| %p    | AM or PM                                                                                                               | (AM or PM)              |
| %r    | Time, 12-hour                                                                                                          | (hh:mm:ss [AP]M)        |
| %s,%S | Seconds                                                                                                                | (00...59)               |
| %T    | Time, 24-hour                                                                                                          | (hh:mm:ss)              |
| %u    | Week, where Monday is the first day of the week                                                                        | (0...52)                |
| %U    | Week, where Sunday is the first day of the week                                                                        | (0...52)                |
| %v    | Week, where Monday is the first day of the week; used with \`%x’                                          \|  (1...53) |                         |
| %V    | Week, where Sunday is the first day of the week; used with\`%X’                                           \| (1...53)  |                         |
| %w    | Day of the week                                                                                                        | (0=Sunday...6=Saturday) |
| %W    | Weekday name                                                                                                           | (Sunday...Saturday)     |
| %x    | Year for the week, where Monday is the first day of the week (numeric, four digits);used with \`%v’       \|  (yyyy)   |                         |
| %X    | Year for the week, where Sunday is the first day of the week (numeric, four digits);used with\`%V’        \| (yyyy)    |                         |
| %y    | Year (numeric, two digits)                                                                                             | (yy)                    |
| %Y    | Year (numeric, four digits)                                                                                            | (yyyy)                  |
| %%    | A literal \`%’                                                                                                         | (%)                     |

* * *

GET_FORMAT(時間格式, 'USA'時間標準) 輸出時間格式(%code)
時間格式：DATE,TIME,DATETIME,TIMESTAMP,YEAR

| 時間標準     |                                  |
| -------- | -------------------------------- |
| EUR      | for Europe                       |
| INTERNAL | for the format in which time     |
| ISO      | for ISO 9075 standard            |
| JIS      | for Japanese Industrial Standard |
| USA      | for United States                |

TIME_TO_SEC(TIME)     時間轉成幾秒(不管日期)
SEC_TO_TIME(SEC(num)) 幾秒轉成時間(只保留時間,最大單位hour)

DATEDIFF(DATE1, DATE2) 日期差距DATE2 - DATE1
TIMEDIFF(TIME1, TIME2) 時間差距TIME2 - TIME1

* * *

### 日期的加減：

DATE_ADD(DATE, INTERVAL '1 DAY') 可以用負數代表DATE_SUB
DATE_SUB(DATE, INTERVAL '1 DAY')
ADDDATE(DATE, NUM) 不需要引數 直接輸入數字 單位DAY
SUBDATE(DATE, NUM)

### 時間的加減：

ADDTIME(TIME, NUM) 不需要引數 直接輸入數字 單位SEC
SUBTIME(TIME, NUM)

### 數值處理函式：

| func            | /                           |
| --------------- | --------------------------- |
| SIGN()          | 正負值,輸出1,0,-1                |
| ABS()           | 絕對值                         |
| SUM()           | 總和                          |
| AVG()           | 平均                          |
| COUNT()         | 計數(不含Null)                  |
| MIN()           | 最小                          |
| MAX()           | 最大                          |
| STDDEV()        | 標準差                         |
| VARIANCE()      | 變異數                         |
| RAND() 0~1      | 隨機小數                        |
| ROUND(FLOAT, n) | 丟棄小數點(4捨5入) , n 保留位數, 不設預設0 |
| FLOOR(NUM)      | 丟棄小數點(無條件)                  |

* * *

### 條件函式：

```SQL
CASE WHEN 條件1 THEN Value1
     WHEN 條件2 THEN Value2
     ELSE Value3
     END

IF(條件, T, F)

IFNULL(T, F)
```

* * *

### 其他函式：

| func          | /        |
| ------------- | -------- |
| DATEBASE()    | 目前資料庫    |
| VERSION()     | MYSQL 版本 |
| USER()        | 目前使用者    |
| PASSWORD(str) | 加密 str   |
