MySQL内建Function

MySQL内建Function

在MySQL存储程序（存储过程、存储Function、触发器）里可以使用几乎任何MySQL内建Function
常用的Function为：

1，字符串function
2，数字function
3，时间和日期function
4，其他function

1，字符串function
字符串function处理VARCHAR/CHAR/TEXT等字符串数据类型

1)ASCII

string = ASCII(string)

返回字符串中首个字符的ASCII码

2)CHAR

string = CHAR(acsii_code[, ...])

返回ASCII码相应的字符

3)CHARSET

character_set = CHARSET(string)

返回字符串对应的字符集

4)CONCAT

string = CONCAT(string [, ...])

返回字符串相加的结果
如果sql_mode='ANSI'，还可以使用||操作符来进行字符串相加

5)CONCAT_WS

string = CONCAT_WS(delimiter, string [, ...])

大致和CONCAT相同，但是会在字符串之间插入delimiter，相当于ruby里的join

6)INSERT

string = INSERT(original_string, position, length, new_string)

在指定position将new_string插入original_string，直到length个字符

7)INSTR

position = INSTR(string, substring)

返回substring在string中第一个出现的position，如果没有找到，则返回0

8)LCASE

string = LCASE(string)

返回string转换为lowercase的形式

9)LEFT

string = LEFT(string, length)

返回string最左边length长度的字符串

10)LENGTH

characters = LENGTH(string)

返回字符串的长度，对于多字节字符串则应该使用CHAR_LENGTH来返回字符的长度而不是字节的长度

11)LOAD_FILE

string = LOAD_FILE(file_name)

读取MySQL服务器上的一个文件并返回合适类型（BLOG或TEXT）

12)LOCATE

position = LOCATE(substring, string [,start_position])

类似于INSTR，它在string里搜索一个substring，并可以知道开始搜索的位置，如果没有找到则返回0

13)LPAD

string = LPAD(string, length, pad)

将pad加到string的左边直到输出的字符串的长度达到length

14)LTRIM

string = LTRIM(string)

去掉string左边所有的空格

15)REPEAT

string = REPEAT(string, count)

返回string重复count次数的字符串

16)REPLACE

string = REPLACE(string, search_string, replace_string)

在string中搜索search_string，用replace_string来替换

17)RPAD

string = RPAD(string, length, pad)

将pad加到string的右边直到输出的字符串的长度达到length

18)RTRIM

string = RTRIM(string)

去掉string右边所有的空格

19)STRCMP

string = STRCMP(string1, string2)

比较两个字符串大小，类似于C里的strcmp函数，返回值为0，-1，1

20)SUBSTRING

string = SUBSTRING(string, position, [,length])

返回从指定position开始length长度的子字符串，position可以为负数

21)TRIM

string = TRIM([[BOTH|LEADING|TRAILING] [padding] FROM] string)

默认trim空格，指定padding则trim padding，可以知道trim前面、后面或两者

22)UCASE

string = UCASE(string)

返回string转换为uppercase的形式

23)其他function

BINARY
BIT_LENGTH
CHAR_LENGTH
CHARACTER_LENGTH
COMPRESS
DECODE
ELT
ENCODE
ENCRYPT
EXPORT_SET
FIELD
INET_ATON
INET_NTOA
LOWER
MID
OCTET_LENGTH
ORD
PASSWORD
POSITION
QUOTE
REVERSE
RIGHT
SHA
SHA1
SOUNDEX
SPACE
SUBSTRING_INDEX
UNCOMPRESSED_LENGTH
UNCOMPRESS
UNHEX
UPPER

2，数字function
数字function处理INT/FLOAT等数字数据类型

1)ABS

number = ABS(number)

返回绝对值

2)BIN

binary_number = BIN(decimal_number)

返回decimal_number的二进制形式

3)CEILING

number = CEILING(numer)

返回稍大的整数

4)CONV

number = CONV(number,from_base,to_base)

将number从from_base进制转换为to_base进制

5)FLOOR

number = FLOOR(number)

返回比number稍小的整数

6)FORMAT

string = FORMAT(number, decimal_places)

使用指定的decimal_place小数位来格式化number，并在每个千位上加上逗号来进行分隔

7)HEX

HexNumber = HEX(DecimalNumber)

返回16进制形式

8)LEAST

number = LEAST(number1, number 2 [, ...])

返回最小的数

9)MOD

remmainder = MOD(numerator, denominator)

求模

10)POW/POWER

result = POWER(number, power)

求幂，power可以为整数、小数、负数

11)RAND

number = RAND([seed])

返回大于0小于1的随机浮点数，指定seed可以用来初始化随机数generator从而避免生产重复的sequence

12)ROUND

integer = ROUND(number [,decimals])

将浮点数转换为最接近的整数，四舍五入，可以指定精确到小数位decimals

13)SIGN

number = SIGN(number)

返回-1、0、1来分别表示负数、0、正数

14)SQRT

number = SQRT(number)

求平方根，相当于POWER(number, .5)

15)其他function

ACOS
ASIN
COT
CRC32
DEGREE2
EXP
LN
LOG
LOG10
LOG2
PI
RADIANS
SIN
TAN

3，时间和日期function
时间和日期function处理DATE/DATETIME等时间数据类型

1)ADDTIME

date = ADDTIME(date, time_interval)

在date的基础上加上time_interval的时间并返回，time_interval的格式为hh:mm:ss.hh

2)CONVERT_TZ

datetime = CONVERT_TZ(datetime, fromTZ, toTZ)

改变时区，合法的时区值可以在mysql.time_zone_name表里找到

3)CURRENT_DATE

date = CURRENT_DATE()

返回当前日期，不包含时间

4)CURRENT_TIME

time = CURRENT_TIME()

放回当前时间，不包含日期

5)CURRENT_TIMESTAMP

datetime = CURRENT_TIMESTAMP()

返回当前日期和时间，格式为yyyy-mm-dd hh:mm:ss

6)DATE

date = DATE(datetime)

返回datetime的date部分

7)DATE_ADD

date = DATE_ADD(date, INTERVAL interval_value interval_type)

在date的基础上加上时间间隔并返回，interval_type可以为:

DAY               dd
DAY_HOUR          ddhh
DAY_MINUTE        ddhhmm
DAY_SECOND        ddhhmmss
HOUR              hh
HOUR_MINUTE       hhmm
HOUR_SECOND       hhmmss
MINUTE            mm
MINUTE_SECOND     mmss
MONTH             mm
SECOND            ss
YEAR              yyyy

8)DATE_FORMAT

string = DATE_FORMAT(datetime, FormatCodes)

格式化datetime，FormatCodes为:

%%                 %
%a                 星期，Mon-Sun
%b                 月，Jan-Dec
%c                 月，1-12
%d                 日期，1-31
%D                 日期，1st, 2nd, 3rd, etc.
%e                 日期，1-31
%h                 小时，1-12
%H                 小时，00-23
%i                 分钟，00-59
%I                 小时，1-12
%j                 天，1-365
%k                 小时，00-23
%l                 小时，1-12
%m                 月，1-12
%M                 月，January-December
%p                 AM/PM
%r                 小时、分钟、秒，12小时制，hh:mm:ss AM|PM
%s                 分钟0-59
%S                 分钟0-59
%T                 小时、分钟、秒，24小时制，HH:mm:ss
%u                 星期，0-52，Monday是一个星期的开始
%U                 星期，0-52，Sunday是一个星期的开始
%v                 星期，1-53，Monday是一个星期的开始
%V                 星期，1-53，Sunday是一个星期的开始
%w                 一个星期的数字表示，0=Sunday，6=Saturday
%W                 星期，Sunday-Saturday
%y                 年，2位数字
%Y                 年，4位数字

9)DATE_SUB

date = DATE_SUB(date, INTERVAL interval_value interval_type)

在date的基础上减去时间间隔并返回，interval_type同上

10)DATEDIFF

days = DATEDIFF(date1, date2)

返回两个date之间的间隔时间，当date1小于date2时结果可以为负数

11)DAY

day = DAY(date)

返回一个月中第几天

12)DAYNAME

day = DAYNAME(date)

返回一个星期中哪一天，Sunday、Monday格式

13)DAYOFWEEK

day = DAYOFWEEK(date)

返回数字表示一个星期中哪一天，1表示Sunday，7表示Saturday

14)DAYOFYEAR

day = DAYOFYEAR(date)

返回一年中第几天，1月1日为1，12月31日为365（如果润年则返回366）

15)EXTRACT

date_part = EXTRACT(interval_name FROM date)

从时间里取出HOUR、YEAR、MONTH、HOUR_SECOND、DAYMINUTE等等

16)GET_FORMAT

format = GET_FORMAT(datetime_type, locale)

返回各种datetime类型+locale对应的format，然后可以拿这个format来给DATE_FORMAT使用
datetime_type可以为:

DATE
TIME
DATETIME
TIMESTAMP

locale可以为:

INTERNAL
ISO
JIS
USA
EUR

17)MAKEDATE

date = MAKEDATE(year, day)

使用年份year和一年的第几天day来返回一个date

18)MAKETIME

time = MAKETIME(hour, minute, second)

使用hour、minute和second返回一个time

19)MONTHNAME

monthname = MONTHNAME(date)

返回给定date的完整的月份名，如July

20)NOW

datetime = NOW()

返回当前日期和时间

21)SEC_TO_TIME

time = SEC_TO_TIME(seconds)

对给定的秒seconds来返回一个time

22)STR_TO_DATE

date = STR_TO_DATE(string, format)

使用一个字符串格式的date/time和一个format来转换成一个date/time类型并返回

23)TIME_TO_SEC

seconds = TIME_TO_SEC(time)

将给定的时间time转换为秒

24)TIMEDIFF

time = TIMEDIFF(datetime1, datetime2)

返回两个datetime之间的间隔时间，当datetime1小于datetime2时结果可以为负的time，格式为(-)hh:mm:ss

25)TIMESTAMP

datetime = TIMESTAMP(date, time)

从指定的date和time返回一个datetime类型

26)TIMESTAMPADD

date_time = TIMESTAMPADD(interval_type, interval_value, date_time)

在date_time的基础上根据interval_type类型加上interval_value的时间间隔，并返回datetime类型的结果

27)TIMESTAMPDIFF

interval_value = TIMESTAMPDIFF(interval_type, date_time1, date_time2)

返回两个datetime的的时间间隔并以interval_type类型来表示

28)WEEK

number = WEEK(date_time [,start_of_week])

返回当前年里到date_time为止有多少个星期，可以指定一个星期的start day，默认为Sunday

29)WEEKDAY

number =WEEKDAY(date)

返回date对应的是一个星期的哪一天的数字表示，Monday为0

30)YEAR

number = YEAR(datetime)

返回datetime的year部分

31)YEARWEEK

YearAndWeek = YEARWEEK(datetime [, StartOfWeek])

返回对给定datetime的year和week，可以指定一个星期的start day，默认为Sunday

32)其他function

ADDDATE
CURDATE
CURTIME
DAYOFMONTH
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MICROSECOND
MINUTE
MONTH
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SUBDATE
SUBTIME
SYSDATE
TO_DAYS
WEEKOFYEAR

4，其他
1)BENCHMARK

zero = BENCHMARK(no_of_repeats, expressions)

重复执行指定的表达式，可以用于MySQL调优

2)COALESCE

value = COALESCE(value [,...])

返回列表里第一个非空的值

3)CURRENT_USER

username = CURRENT_USER()

返回当前MySQL用户的用户名和主机名

4)DATEBASE

database_name = DATABASE()

返回当前使用的数据库名字

5)GET_LOCK

return_code = GET_LOCK(lock_name, timeout)

给定名字lock_name来定义锁，返回锁号码

6)RELEASE_LOCK

return_code = RELEASE_LOCK(lock_name)

解锁，返回锁号码

7)IS_FREE_LOCK

integer = IS_FREE_LOCK(lock_name)

如果用户定义的名为lock_name的锁可以获得，则返回1，否则返回0

8)IFNULL

value = IFNULL(value, nullvalue)

如果value不为NULL则返回value，否则返回第二个参数nullvalue

9)INTERVAL

position = INTERVAL(search, number, ...)

返回search应该在后面的列表的位置，从0开始，后面的列表应该为升序

10)ISNULL

integer = ISNULL(value)

如果value是NULL则返回1，否则返回0

11)NULLIF

value = NULLIF(value1, value2)

如果value1和value2相等，则返回NULL，否则返回value1

12)SESSION_USER
同USER

13)SYSTEM_USER
同USER

14)USER

username = USER()

返回当前MySQL连接的用户名和主机名，表示用于建立连接的用户名和主机名，而CURRENT_USER表示mysql.user表里实际上使用的用户名

15)UUID

string = UUID()

返回128位的Universal Unique Identifier(UUID)，唯一值，部分来自你的计算机名、当前日期和时间。

16)VERSION

string = VERSION()

返回当前MySQL服务器的版本

评论

1 楼 judytang 2008-05-25  

好一篇博文就像MYSQL的一个缩写本，好认真，谢谢分享，SUN前不久才买了MYSQL，在中国MYSQL用户多吗，你觉得MYSQL好用吗，对了你用GLASSFISH吗，我是跟GLASSFISH打很多交道的人，向你学习啦 。。。