MySQL functions Daquan

String functions
ASCII (str)
Returns the ASCII value of the first character of the string str (str is empty when the string returns 0)
Mysql> Select ASCII (' 2 ');
50
Mysql> Select ASCII (2);
50
Mysql> Select ASCII (' dete ');
100

ORD (str)
If the string str first is a single byte, returns the same value as the ASCII () function.
If it is a multibyte character, return in format (first byte ASCII code) *256+ (second byte ASCII code)) [*256+third byte ASCII code ...]
Mysql> Select ORD (' 2 ');
50

CONV (N,from_base,to_base)
Converts the number n into a string return (NULL is returned if any argument is null, the binary range is 2-36, and n is the unsigned number if to_base is negative), and Conv works with 64-bit precision.
Mysql> Select CONV ("a", 16,2);
' 1010 '
Mysql> Select CONV ("6E", 18,8);
' 172 '
Mysql> Select CONV ( -17,10,-18);
'-H '
Mysql> Select CONV (+ + ' + ' +0xa,10,10);
' 40 '

BIN (N)
Turns n into a binary value and returns with a string (n is a bigint number, equivalent to Conv
(n,10,2))
Mysql> Select BIN (12);
' 1100 '

OCT (N)
Convert N to octal value and return with string (n is bigint number, equivalent to Conv
(n,10,8))
Mysql> Select OCT (12);
' 14 '

HEX (N)
Convert N to hex and return with string (n is bigint number, equivalent to Conv
(n,10,16))
Mysql> Select HEX (255);
' FF '

CHAR (N,...)
Returned by parameter N, ... A string of corresponding ASCII code characters (the parameter is n,... is a sequence of numbers, and null values are skipped)
Mysql> Select CHAR (77,121,83,81, ' 76 ');
' MySQL '
Mysql> Select CHAR (77,77.3, ' 77.3 ');
' MMM '

CONCAT (STR1,STR2,...)
The parameter is linked to a long string and returned (null if any argument is null)

Mysql> Select CONCAT (' My ', ' S ', ' QL ');
' MySQL '
Mysql> Select CONCAT (' My ', NULL, ' QL ');
, NULL
Mysql> Select CONCAT (14.3);
' 14.3 '

LENGTH (str)
Octet_length (str)
Char_length (str)
Character_length (str)
Returns the length of the string str (calculated once for multibyte character Char_length only)

Mysql> Select LENGTH (' text ');
4
Mysql> Select Octet_length (' text ');
4

LOCATE (SUBSTR,STR)
POSITION (substr in str)
Returns the string substr the first occurrence of the string str (str returns 0 if it does not contain substr)
Mysql> Select LOCATE (' Bar ', ' Foobarbar ');
4
Mysql> Select LOCATE (' Xbar ', ' foobar ');
0

LOCATE (Substr,str,pos)
Returns the position of the first occurrence of the string substr in the POS position of the string str (str does not contain substr when it returns 0)
Mysql> Select LOCATE (' Bar ', ' Foobarbar ', 5);
7

INSTR (STR,SUBSTR)
Returns the string substr the first occurrence of the string str (str returns 0 if it does not contain substr)
Mysql> Select INSTR (' Foobarbar ', ' Bar ');
4
Mysql> Select INSTR (' Xbar ', ' foobar ');
0

Lpad (STR,LEN,PADSTR)
Fills str with string padstr until the string length is Len and returns
Mysql> Select Lpad (' Hi ', 4, '?? ');
'?? Hi

Rpad (STR,LEN,PADSTR)
Fills the right end of Str with a string padstr until the string length is Len and returns
Mysql> Select Rpad (' Hi ', 5, '? ');
' Hi??? '

Left (Str,len)
Returns the left Len character of a string str
Mysql> Select Left (' Foobarbar ', 5);
' Fooba '

Right (Str,len)
Returns the right-side Len character of a string str
Mysql> Select Right (' Foobarbar ', 4);
' Rbar '

SUBSTRING (Str,pos,len)
SUBSTRING (str from POS for Len)
MID (Str,pos,len)
Returns the position of the string str at POS from Len characters (the ugly syntax using from IS ANSI SQL92 standard)
Mysql> Select SUBSTRING (' quadratically ', 5,6);
' Ratica '

SUBSTRING (Str,pos)
SUBSTRING (str from POS)
Returns a substring of the position of the string str from POS
Mysql> Select SUBSTRING (' quadratically ', 5);
' Ratically '
Mysql> Select SUBSTRING (' Foobarbar ' from 4);
' Barbar '

Substring_index (Str,delim,count)
Returns the substring from the count of the string str that appears after the delimiter Delim (the left end is returned when Count is positive, otherwise the right terminal string is returned)
Mysql> Select Substring_index (' www.mysql.com ', '. ', 2);
' Www.mysql '
Mysql> Select Substring_index (' www.mysql.com ', '. ',-2);
' MySQL.com '

LTRIM (str)
Returns the string with the left space removed str
Mysql> Select LTRIM (' Barbar ');
' Barbar '

RTRIM (str)
Returns the string with the right space removed str
Mysql> Select RTRIM (' Barbar ');
' Barbar '

TRIM ([[BOTH | Leading | TRAILING] [REMSTR] from] str)
Returns the prefix or suffix remstr the deleted string str (position parameter default Both,remstr default value is a space)
Mysql> Select TRIM (' Bar ');
' Bar '
Mysql> Select TRIM (Leading ' x ' from ' xxxbarxxx ');
' Barxxx '
Mysql> Select TRIM (BOTH ' x ' from ' xxxbarxxx ');
' Bar '
Mysql> Select TRIM (TRAILING ' xyz ' from ' barxxyz ');
' Barx '

SOUNDEX (str)
Returns a string that sounds like "roughly the same" to a string with the same string, non-alphanumeric characters are ignored, and letters outside A-Z are used as vowels.
Mysql> Select SOUNDEX (' Hello ');
' H400 '
Mysql> Select SOUNDEX (' quadratically ');
' Q36324 '

SPACE (N)
Returns a string consisting of n space characters
Mysql> Select SPACE (6);
""

REPLACE (STR,FROM_STR,TO_STR)
Replaces substrings in string str with string To_str from_str and returns
Mysql> Select REPLACE (' www.mysql.com ', ' w ', ' Ww ');
' WwWwWw.mysql.com '

REPEAT (Str,count)
Returns a string that is linked by Count string Str (returns an empty string when null,count<=0 is returned when any parameter is null)
Mysql> Select REPEAT (' MySQL ', 3);
' Mysqlmysqlmysql '

REVERSE (str)
Reverses the character order of the string str and returns
Mysql> Select REVERSE (' abc ');
' CBA '

INSERT (STR,POS,LEN,NEWSTR)
The string str is replaced with a string newstr by a substring of position pos, Len characters long, and returns
Mysql> Select INSERT (' Quadratic ', 3, 4, ' what ');
' Quwhattic '

ELT (N,STR1,STR2,STR3,...)
Returns the nth string (n is less than 1 or greater than the number of arguments returns NULL)
Mysql> Select ELT (1, ' ej ', ' Heja ', ' Hej ', ' foo ');
' EJ '
Mysql> Select ELT (4, ' ej ', ' Heja ', ' Hej ', ' foo ');
' Foo '

FIELD (STR,STR1,STR2,STR3,...)
Returns the ordinal of the nth string after str equals (if STR is not found returns 0)
Mysql> Select FIELD (' EJ ', ' Hej ', ' ej ', ' Heja ', ' Hej ',
' foo ');
2
Mysql> Select FIELD (' fo ', ' Hej ', ' ej ', ' Heja ', ' Hej ',
' foo ');
0

Find_in_set (Str,strlist)
Returns the ordinal of STR in the string set Strlist (any parameter is NULL, returns NULL if STR does not find return 0, parameter 1 contains "," when working abnormally)
mysql> SELECT find_in_set (' B ', ' a,b,c,d ');
2

Make_set (BITS,STR1,STR2,...)
Convert the number of the parameter 1 to binary, if the bits of a position equals 1, the string of the corresponding position is selected in the string set and returned (the null string is not added to the result)
Mysql> SELECT make_set (1, ' A ', ' B ', ' C ');
' A '
Mysql> SELECT make_set (1 | 4, ' hello ', ' nice ', ' world ');
' Hello,world '
mysql> SELECT make_set (0, ' a ', ' B ', ' C ');
""

Export_set (Bits,on,off,[separator,[number_of_bits])
Sets the set of strings by bits, inserting off (separator default ",", number_of_bits parameter is less than 0 when the bit equals 1 o'clock insert string on)
And too long truncation)
Mysql> Select Export_set (5, ' Y ', ' N ', ', ', 4)
Y,n,y,n

LCASE (str)
LOWER (str)
Returns the lowercase string str
Mysql> Select LCASE (' quadratically ');
' Quadratically '

UCASE (str)
UPPER (str)
Returns the uppercase String str
Mysql> Select UCASE (' quadratically ');
' Quadratically '

Load_file (file_name)
Reads the file and returns the file contents as a string (the file cannot be found, the path is incomplete, no permissions, and the length is greater than Max_allowed_packet will return null)
mysql> UPDATE table_name SET blob_column=load_file
("/tmp/picture") WHERE id=1;

Mathematical functions
ABS (N)
Returns the absolute value of n
Mysql> Select ABS (2);
2
Mysql> Select ABS (-32);
32

Sign (N)
Returns the symbol for the parameter (1, 0, or 1)
Mysql> Select sign (-32);
-1
Mysql> Select sign (0);
0
Mysql> Select sign (234);
1

MOD (N,M)
Modulo operation, returns the remainder of n being removed by M (same as% operator)
Mysql> Select MOD (234, 10);
4
Mysql> Select 234% 10;
4
Mysql> Select MOD (29,9);
2

Floor (N)
Returns the maximum integer value not greater than n
Mysql> Select Floor (1.23);
1
Mysql> Select Floor (-1.23);
-2

CEILING (N)
Returns the smallest integer value not less than n
Mysql> Select CEILING (1.23);
2
Mysql> Select CEILING (-1.23);
-1

ROUND (N,D)
Returns the rounding value of N, leaving the D decimal number (the default value of D is 0)
Mysql> Select ROUND (-1.23);
-1
Mysql> Select ROUND (-1.58);
-2
Mysql> Select ROUND (1.58);
2
Mysql> Select ROUND (1.298, 1);
1.3
Mysql> Select ROUND (1.298, 0);
1

EXP (N)
Returns the n-th square of the value E (the base of the natural logarithm)
Mysql> Select EXP (2);
7.389056
Mysql> Select EXP (-2);
0.135335

LOG (N)
Returns the natural logarithm of n
Mysql> Select LOG (2);
0.693147
Mysql> Select LOG (-2);
, NULL

LOG10 (N)
Returns the logarithm of n at base 10
Mysql> Select LOG10 (2);
0.301030
Mysql> Select LOG10 (100);
2.000000
Mysql> Select LOG10 (-100);
, NULL

POW (x, y)
POWER (x, y)
The Y-power of the return value X
Mysql> Select POW (2,2);
4.000000
Mysql> Select POW (2,-2);
0.250000

SQRT (N)
Returns the square root of a non-negative n
Mysql> Select SQRT (4);
2.000000
Mysql> Select SQRT (20);
4.472136

PI ()
return pi
Mysql> select PI ();
3.141593

COS (N)
Returns the cosine value of n
Mysql> Select COS (PI ());
-1.000000

SIN (N)
Returns the sinusoidal value of n
Mysql> Select SIN (PI ());
0.000000

TAN (N)
Returns the tangent value of n
Mysql> Select TAN (PI () +1);
1.557408

ACOS (N)
Returns the n inverse cosine (n is the cosine value, in the range 1 to 1, otherwise null is returned)
Mysql> Select ACOS (1);
0.000000
Mysql> Select ACOS (1.0001);
, NULL
Mysql> Select ACOS (0);
1.570796

ASIN (N)
Returns n Inverse sine value
Mysql> Select ASIN (0.2);
0.201358
Mysql> Select ASIN (' foo ');
0.000000

ATAN (N)
Returns the inverse tangent value of n
Mysql> Select ATAN (2);
1.107149
Mysql> Select ATAN (-2);
-1.107149
ATAN2 (x, y)
Returns 2 variables x and y of the inverse tangent (similar to the y/x tangent, the symbol determines the quadrant)
Mysql> Select ATAN ( -2,2);
-0.785398
Mysql> Select ATAN (PI (), 0);
1.570796

COT (N)
Returns the cotangent of X
Mysql> Select COT (12);
-1.57267341
Mysql> Select COT (0);
, NULL

RAND ()
RAND (N)
Returns a random floating-point value within a range of 0 to 1.0 (you can use the number n as the initial value)

Mysql> select RAND ();
0.5925
Mysql> Select RAND (20);
0.1811
Mysql> Select RAND (20);
0.1811
Mysql> select RAND ();
0.2079
Mysql> select RAND ();
0.7888

DEGREES (N)
Transforms n from radians to angles and returns
Mysql> Select DEGREES (PI ());
180.000000

RADIANS (N)
Transform n from angle to radians and return
Mysql> Select RADIANS (90);
1.570796

TRUNCATE (N,D)
Retains the D decimal number of the digit N and returns
Mysql> Select TRUNCATE (1.223,1);
1.2
Mysql> Select TRUNCATE (1.999,1);
1.9
Mysql> Select TRUNCATE (1.999,0);
1

LEAST (x, y,...)
Returns the minimum value (if the return value is used in an integer (real or size-sensitive string) context or if all parameters are integers (real or size-sensitive strings) then they are compared as integers (real or size-sensitive strings), otherwise by the case-insensitive string is compared)
Mysql> Select LEAST (2,0);
0
Mysql> Select LEAST (34.0,3.0,5.0,767.0);
3.0
Mysql> Select LEAST ("B", "A", "C");
"A"

Greatest (x, Y,...)
Returns the maximum value (the rest of the same least ())
Mysql> Select Greatest (2,0);
2
Mysql> Select Greatest (34.0,3.0,5.0,767.0);
767.0
Mysql> Select Greatest ("B", "A", "C");
"C"

Period Time function
DAYOFWEEK (date)
The return date is the day of the week (1= Sunday, 2 = Monday,...... 7= Saturday, ODBC
Standard
Mysql> Select DAYOFWEEK (' 1998-02-03 ');
3

WEEKDAY (date)
The return date is the day of the week (0= Monday, 1 = Tuesday,...... 6= Sunday).

Mysql> Select WEEKDAY (' 1997-10-04 22:23:00 ');
5
Mysql> Select WEEKDAY (' 1997-11-05 ');
2

DayOfMonth (date)
Return date is the day of the January (within the range of 1 to 31)
Mysql> Select DayOfMonth (' 1998-02-03 ');
3

DayOfYear (date)
Return date is the day of the year (within the range of 1 to 366)
Mysql> Select DayOfYear (' 1998-02-03 ');
34

MONTH (date)
Returns the month value in date
Mysql> Select MONTH (' 1998-02-03 ');
2

Dayname (date)
Returns the day of the week (returned by English name)
Mysql> Select Dayname ("1998-02-05");
' Thursday '

MONTHNAME (date)
Returns a date that is a few months (returned by English name)
Mysql> Select MONTHNAME ("1998-02-05");
' February '

QUARTER (date)
Returns the first quarter of a year for date
Mysql> Select QUARTER (' 98-04-01 ');
2

WEEK (Date,first)
Return date is the week ordinal of the year (first default 0,first value of 1 indicates that Monday is the beginning of the week, 0 starting from Sunday)
Mysql> Select WEEK (' 1998-02-20 ');
7
Mysql> Select WEEK (' 1998-02-20 ', 0);
7
Mysql> Select WEEK (' 1998-02-20 ', 1);
8

Year (date)
Returns the date of the year (in the range 1000 to 9999)
Mysql> Select year (' 98-02-03 ');
1998

HOUR (Time)
Number of hours to return time (range 0 to 23)
Mysql> Select HOUR (' 10:05:03 ');
10

MINUTE (Time)
Number of minutes to return time (range 0 to 59)
Mysql> Select MINUTE (' 98-02-03 10:05:03 ');
5

SECOND (Time)
Returns the number of seconds in time (range 0 to 59)
Mysql> Select SECOND (' 10:05:03 ');
3

Period_add (P,n)
Add n months to a period p and return (P's format yymm or YYYYMM)
Mysql> Select Period_add (9801,2);
199803

Period_diff (P1,P2)
Returns the number of months between the period P1 and P2 (format yymm or yyyymm of P1 and P2)
Mysql> Select Period_diff (9802,199703);
11

Date_add (Date,interval expr type)
Date_sub (date,interval expr type)
Adddate (date,interval expr type)
Subdate (Date,interval expr type)
adds and subtracts dates and times
(Adddate () and subdate () are synonyms of date_add () and date_sub (), or you can use the operator +　And-instead of the function
Date is a datetime or date value, a table in which expr adds and subtracts a date
a string of type that indicates how the expression expr should be interpreted
[type value meaning expected expr format]:
SECOND sec SECONDS
MINUTE min MINUTES
HOUR time HOURS
Day days
Month month MONTHS
year years
Minute_sec OND minutes and seconds "minutes:seconds"
Hour_minute hours and minutes "hours:minutes"
Day_hour Day and Hour "days HOURS"
Year_month year and month years- MONTHS "
Hour_second hours, minutes," HOURS:MINUTES:SECONDS "
Day_minute days, hours, minutes" days hours:minutes "
Day_second day, Hour,　Minutes, seconds "Days
HOURS:MINUTES:SECONDS"
Expr allows any punctuation to be delimited, if all is a date value when the result is a
date value, otherwise the result is a datetime value)
If the type keyword is incomplete, then MySQL takes value from the right end, day_second because the missing
less hours minutes equals Minute_second)
If you increase month, year_month, or year, the number of days is greater than the maximum day of the result month
The maximum number of days to use)
Mysql> select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;

-1998-01-01 00:00:00
Mysql> Select INTERVAL 1 day + "1997-12-31";
1998-01-01
mysql> Select "1998-01-01"-INTERVAL 1 SECOND;
-1997-12-31 23:59:59
Mysql> Select Date_add ("1997-12-31 23:59:59", INTERVAL 1
SECOND);
-1998-01-01 00:00:00
Mysql> Select Date_add ("1997-12-31 23:59:59", INTERVAL 1
Day);
-1998-01-01 23:59:59
Mysql> Select Date_add ("1997-12-31 23:59:59", INTERVAL
"1:1" minute_second);
-1998-01-01 00:01:00
Mysql> Select Date_sub ("1998-01-01 00:00:00", INTERVAL "1
1:1:1 "Day_second);
-1997-12-30 22:58:59
Mysql> Select Date_add ("1998-01-01 00:00:00", INTERVAL "-1
"Day_hour");
-1997-12-30 14:00:00
Mysql> Select Date_sub ("1998-01-02", INTERVAL);
1997-12-02
Mysql> Select EXTRACT (Year from "1999-07-02");
1999
Mysql> Select EXTRACT (year_month from "1999-07-02
01:02:03 ");
199907
Mysql> Select EXTRACT (Day_minute from "1999-07-02
01:02:03 ");
20102

To_days (date)
Return date dates is 00 to present how many days (not calculated before 1582)
Mysql> Select To_days (950501);
728779
Mysql> Select To_days (' 1997-10-07 ');
729669

From_days (N)
Given the number of days 00 to date returns the date value (not calculated until 1582)
Mysql> Select From_days (729669);
' 1997-10-07 '

Date_format (Date,format)
Formatting a date value based on the format string
(a marker is available in the format string:
%M month name (January ... December)
%W Week name (Sunday ... Saturday)
%d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). ）
%Y year, number, 4 bit
%y year, number, 2 bit
%a abbreviated weekday name (Sun ... Sat)
Number of days in the month of%d, number (00 ...). 31)
Number of days in%e month, number (0 ... 31)
%m Month, number (01 ... 12)
%c month, number (1 ... 12)
%b Abbreviated month name (Jan ... DEC)
%j Days of the year (001 ... 366)
%H hours (00 ... 23)
%k hours (0 ... 23)
%h hours (01 ... 12)
%I Hours (01 ... 12)
%l hours (1 ... 12)
%i minutes, Numbers (00 ... 59)
%r time, 12 hours (Hh:mm:ss [ap]m)
%T time, 24 hours (HH:MM:SS)
%s seconds (00 ... 59)
%s seconds (00 ... 59)
%p am or PM
%w days in one weeks (0=sunday ... 6=saturday)
%u Week (0 ... 52), here Sunday is the first day of the week
%u Week (0 ... 52), here Monday is the first day of the week
% of percent characters)
Mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%W%M%
Y ');
' Saturday October 1997 '
Mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%h:%i:%
S ');
' 22:23:00 '
Mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%d%y%a
%d%m%b%j ');
4th Sat Oct 277 '
Mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%H%k%I
%r%T%s%w ');
-10:23:00 PM 22:23:00 00 6 '

Time_format (Time,format)
Similar to Date_format (), but Time_format only handles hours, minutes, and seconds (its
The remainder symbol produces a null value or 0)

Curdate ()
Current_date ()
Returns the current date value in ' Yyyy-mm-dd ' or YYYYMMDD format (a string or number based on the context in which the return value is located)
Mysql> select Curdate ();
' 1997-12-15 '
Mysql> Select curdate () + 0;
19971215

Curtime ()
Current_time ()
Returns the current time value in ' HH:MM:SS ' or HHMMSS format (a string or number based on the context in which the return value is located)
Mysql> select Curtime ();
' 23:50:26 '
Mysql> Select Curtime () + 0;
235026

Now ()
Sysdate ()
Current_timestamp ()
returns the current datetime in ' yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format (a string or number based on the context in which the return value is located)
Mysql> Select Now ();
' 1997-12-15 23:50:26 '
Mysql> Select now () + 0;
19971215235026

Unix_timestamp ()
Unix_timestamp (date)
Returns a UNIX timestamp (the number of seconds starting from ' 1970-01-01 00:00:00 ' GMT, the date default is the current time)
Mysql> select Unix_timestamp ();
882226357
Mysql> Select Unix_timestamp (' 1997-10-04 22:23:00 ');
875996580

From_unixtime (Unix_timestamp)
Returns the timestamp value in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format (a string or number based on the context in which the return value is located)
Mysql> Select From_unixtime (875996580);
' 1997-10-04 22:23:00 '
Mysql> Select From_unixtime (875996580) + 0;
19971004222300

From_unixtime (Unix_timestamp,format)
Returns the timestamp value as format string
Mysql> Select From_unixtime (Unix_timestamp (), '%Y%d%M%
h:%i:%s%x ');
' 1997 23rd December 03:43:30 X '

Sec_to_time (seconds)
A time value that returns the number of seconds in ' HH:MM:SS ' or HHMMSS format (a string or number based on the context in which the return value is located)
Mysql> Select Sec_to_time (2378);
' 00:39:38 '
Mysql> Select Sec_to_time (2378) + 0;
3938

Time_to_sec (Time)
Returns how many seconds the time value is
Mysql> Select Time_to_sec (' 22:23:00 ');
80580
Mysql> Select Time_to_sec (' 00:39:38 ');
2378

Conversion functions
Cast
Usage: Cast (field as data type) [of course, whether it can be converted successfully, but also to see the issue of the data type forced conversion]
Example: Select cast (A as unsigned) as B from Cardserver where ORDER by b Desc;
Convert
Usage: CONVERT (field, data type)
Example: Select CONVERT (A, unsigned) as B from Cardserver where ORDER by b Desc;

MySQL functions Daquan