MySQL functions and usage examples _ MySQL

MySQL functions and Usage examples

String functions

ASCII (str)
Returns the ASCII value of the first character of the string 'str' (0 is returned when 'str' is an empty string)
Mysql> select ASCII ('2 ');
-> 50
Mysql> select ASCII (2 );
-> 50
Mysql> select ASCII ('dete ');
-> 100

ORD (str)

If the string 'str' is at the beginning of a single byte, return the same value as that returned by the ASCII () function.

If it is a multi-byte character, return in the 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 to a string and returns the result (if any parameter is NULL ).

NULL. the hexadecimal range is 2-36. if to_base is a negative number, N is used as the signed number. otherwise

As the number of unsigned values, CONV works with 64-point precision)
Mysql> select CONV ("a", 16, 2 );
-> '123'
Mysql> select CONV ("6E", 18, 8 );
-> '123'
Mysql> select CONV (-17,10,-18 );
-> '-H'
Mysql> select CONV (10 + "10" + '10' + 0xa, 10, 10 );
-> '40'

BIN (N)
Convert N to a binary value and return it as a string (N is a BIGINT number, equivalent to CONV

(N, 10, 2 ))
Mysql> select BIN (12 );
-> '123'

OCT (N)
Convert N to an octal value and return it as a string (N is a BIGINT number, equivalent to CONV

(N, 10, 8 ))
Mysql> select OCT (12 );
-> '14'

HEX (N)
Convert N to hexadecimal notation and return it as a string (N is a BIGINT number, equivalent to CONV

(N, 10, 16 ))
Mysql & gt; select HEX (255 );
-> 'Ff'

CHAR (N ,...)
Returns a string consisting of ASCII code characters (the parameter is

N,... is a numerical sequence, and the NULL value is skipped)
Mysql> select CHAR (77,121, 81, '76 ');
-> 'Mysql'
Mysql> select CHAR (77, 77.3, '77. 3 ');
-> 'Mmm'

CONCAT (str1, str2 ,...)
Concatenate a parameter into a long string and return it (if any parameter is NULL, return 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 str string (for multi-byte characters, CHAR_LENGTH is calculated only once)

Mysql> select LENGTH ('text ');
-> 4
Mysql> select OCTET_LENGTH ('text ');
-> 4

LOCATE (substr, str)
POSITION (substr IN str)
Returns the position where the string substr appears for the first time (str does not contain

Returns 0 when substr is used)
Mysql> select LOCATE ('bar', 'foobarbar ');
-> 4
Mysql> select LOCATE ('xbar', 'foobar ');
-> 0

LOCATE (substr, str, pos)
Returns the first occurrence position of the string substr at the position pos of the string str.

(Return 0 if str does not contain substr)
Mysql> select LOCATE ('bar', 'foobarbarbar ', 5 );
-> 7

INSTR (str, substr)
Returns the position where the string substr appears for the first time (str does not contain

Returns 0 when substr is used)
Mysql> select INSTR ('foobar', 'bar ');
-> 4
Mysql> select INSTR ('xbar', 'foobar ');
-> 0

LPAD (str, len, padstr)
Fill the left end of str with the string padstr until the string length is len and return
Mysql> select LPAD ('hi', 4 ,'?? ');
-> '?? Hi'

RPAD (str, len, padstr)
Fill the right end of str with the string padstr until the string length is len and return
Mysql> select RPAD ('hi', 5 ,'? ');
-> 'Hi ??? '

LEFT (str, len)
Returns the left len of the str string.
Mysql> select LEFT ('foobarbar', 5 );
-> 'Fooba'

RIGHT (str, len)
Returns the right len of the str string.
Mysql> select RIGHT ('foobarbar', 4 );
-> 'Rbar'

SUBSTRING (str, pos, len)
SUBSTRING (str FROM pos FOR len)
MID (str, pos, len)
Returns the string 'str' position. pos starts with len characters (the ugly syntax of FROM IS

ANSI SQL92 standard)
Mysql> select SUBSTRING ('quadratically ', 5, 6 );
-> 'Ratica'

SUBSTRING (str, pos)
SUBSTRING (str FROM pos)
Returns a substring starting from the position pos of the str string.
Mysql> select SUBSTRING ('quadratically ', 5 );
-> 'Ratically'
Mysql> select SUBSTRING ('foobarbar' FROM 4 );
-> 'Barbar'

SUBSTRING_INDEX (str, delim, count)
Returns the substring after the delimiter delim that appears from the count of the str string.

(If count is a positive number, return the left end; otherwise, return the right terminal string)
Mysql> select SUBSTRING_INDEX ('www .mysql.com ','. ', 2 );
-> 'Www. mysql'
Mysql> select SUBSTRING_INDEX ('www .mysql.com ','. ',-2 );
-> 'MySQL. com'

LTRIM (str)
Returns the str string with the left space deleted.
Mysql> select LTRIM ('barbar ');
-> 'Barbar'

RTRIM (str)
Returns the str string with the right space deleted.
Mysql> select RTRIM ('barbar ');
-> 'Barbar'

TRIM ([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
Returns the str string whose prefix or suffix is deleted by remstr (default location parameter ).

BOTH, remstr. the default value is 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 same-tone string of str (which sounds "roughly the same" and has the same string

Homophone string, non-alphanumeric characters ignored, letters outside the A-Z are treated as vowels)
Mysql> select SOUNDEX ('Hello ');
-> 'H400'
Mysql> select SOUNDEX ('quadratically ');
-> 'Q36324'

SPACE (N)
Returns a string consisting of N spaces.
Mysql> select SPACE (6 );
->''

REPLACE (str, from_str, to_str)
Replace the child string from_str with the to_str string and return
Mysql> select REPLACE ('www .mysql.com ', 'W', 'WW ');
-> 'Wwwwww .mysql.com'

REPEAT (str, count)
Returns a string consisting of str strings (when any parameter is NULL ).

Return NULL, count <= 0 returns an empty string)
Mysql> select REPEAT ('mysql', 3 );
-> 'Mysqlmysqlmysqlmysql'

REVERSE (str)
Reverse the character order of string 'str' and return
Mysql> select REVERSE ('ABC ');
-> 'CBA'

INSERT (str, pos, len, newstr)
Replace the string 'str' with a string of len characters starting from the position pos.

Newstr and return
Mysql> select INSERT ('quadratic ', 3, 4, 'wh ');
-> 'Quwhattic'

ELT (N, str1, str2, str3 ,...)
Returns the nth string (N less than 1 or greater than the number of parameters 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 number of the nth string after which str is equal (if str is not found, 0 is returned)
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 serial number of str in the strlist of the string set. (if any parameter is NULL, return

NULL. if 'str' is not found, return 0. if parameter 1 contains ",", it will work abnormally)
Mysql> SELECT FIND_IN_SET ('B', 'a, B, c, D ');
-> 2

MAKE_SET (bits, str1, str2 ,...)
Convert the number of parameter 1 to binary. if the binary bit at a certain position is equal to 1

Position string is selected and returned (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])
Sort string sets by bits. only when the bit is equal to 1 will the string on be inserted. Otherwise, the string will be inserted.

Off (default value of separator ",", number_of_bits parameter is used when the length is not enough to add 0

Excessive truncation)
Mysql> select EXPORT_SET (5, 'y', 'n', ',', 4)
-> Y, N, Y, N

LCASE (str)
LOWER (str)
Returns the str string in lower case.
Mysql> select LCASE ('quadratically ');
-> 'Quadratically'

UCASE (str)
UPPER (str)
Returns an uppercase str string.
Mysql> select UCASE ('quadratically ');
-> 'Quadratically'

LOAD_FILE (file_name)
Read the file and return the file content as a string (the file cannot be found, path

Incomplete, no permission. if the length is greater than max_allowed_packet, NULL is returned)
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)
Symbol of the returned parameter (-1, 0, or 1)
Mysql> select SIGN (-32 );
->-1
Mysql> select SIGN (0 );
-> 0
Mysql> select SIGN (1, 234 );
-> 1

MOD (N, M)
Modulo operation returns the remainder of N divided by M (same as the % 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 & gt; 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 and retains the D decimal places (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 nth power of 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 base-10 logarithm of N.
Mysql> select LOG10 (2 );
-> 0.301030
Mysql> select LOG10 (100 );
-> 2.000000
Mysql> select LOG10 (-100 );
-> NULL

POW (X, Y)
POWER (X, Y)
Returns the Y power of X.
Mysql> select POW (2, 2 );
-> 4.000000
Mysql> select POW (2,-2 );
-> 0.250000

SQRT (N)
Returns the square root of non-negative N.
Mysql> select SQRT (4 );
-> 2.000000
Mysql> select SQRT (20 );
-> 4.472136

PI ()
Returns the circumference rate.
Mysql> select PI ();
-> 3.141593

COS (N)
Returns the cosine of N.
Mysql> select COS (PI ());
->-1.000000

SIN (N)
Returns the sine of N.
Mysql> select SIN (PI ());
-> 0.000000

TAN (N)
Returns the tangent of N.
Mysql> select TAN (PI () + 1 );
-> 1.557408

ACOS (N)
Returns the inverse cosine of N (N is the cosine value in the range of-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 the inverse sine of N.
Mysql> select ASIN (0.2 );
-> 0.201358
Mysql> select ASIN ('Foo ');
-> 0.000000

ATAN (N)
Returns the arc tangent of N.
Mysql> select ATAN (2 );
-> 1.107149
Mysql> select ATAN (-2 );
->-1.107149
ATAN2 (X, Y)
Returns the arc tangent of two variables X and Y (similar to the arc tangent of Y/X, the symbol determines the quadrant)
Mysql> select ATAN (-2, 2 );
->-0.785398
Mysql> select ATAN (PI (), 0 );
-> 1.570796

COT (N)
Returns the tangent of X.
Mysql> select COT (12 );
->-1.57267341
Mysql> select COT (0 );
-> NULL

RAND ()
RAND (N)
Returns a random floating point value ranging from 0 to 1.0 (the number N can be used 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)
Converts N from radians to degrees and returns
Mysql> select DEGREES (PI ());
-> 180.000000

RADIANS (N)
Converts N from angle to Radian and returns
Mysql> select RADIANS (90 );
-> 1.570796

TRUNCATE (N, D)
Retain the D decimal places of the number N and return
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 number or size sensitive string ).

And all parameters are integers (real numbers or size-sensitive strings ).

Number or size of sensitive strings), otherwise, strings that are case-insensitive are 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 ");
-> ""

GREATEST (X, Y ,...)
Returns the maximum value (other values are the same as 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 functions
DAYOFWEEK (date)
The return date is the day of the week (1 = Sunday, 2 = Monday ,...... 7 = Saturday, ODBC

Standard)
Mysql> select DAYOFWEEK ('2017-02-03 ');
-> 3

WEEKDAY (date)
Returns the date of a week (0 = Monday, 1 = Tuesday ,...... 6 = Sunday ).

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

DAYOFMONTH (date)
Returns the day (within the range of 1 to 31) from January 1, January)
Mysql> select DAYOFMONTH ('2017-02-03 ');
-> 3

DAYOFYEAR (date)
Returns the day (within the range of 1 to 366) of a year)
Mysql> select DAYOFYEAR ('2017-02-03 ');
-> 34

MONTH (date)
Returns the month value in date.
Mysql> select MONTH ('2014-02-03 ');
-> 2

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

MONTHNAME (date)
Returns the month of the date value (returned by English name)
Mysql> select MONTHNAME ("1998-02-05 ");
-> 'February'

QUARTER (date)
Returns the quarter of the year for date.
Mysql> select QUARTER ('98-04-01 ');
-> 2

WEEK (date, first)
Returns the week number of the year for date (first defaults to 0, first value 1 indicates Monday is

The beginning of the week, 0 from Sunday)
Mysql> select WEEK ('2017-02-20 ');
-> 7
Mysql> select WEEK ('2017-02-20 ', 0 );
-> 7
Mysql> select WEEK ('2017-02-20 ', 1 );
-> 8

YEAR (date)
Returns the year of date (range: 1000 to 9999)
Mysql> select YEAR ('98-02-03 ');
-> 1998

HOUR (time)
Returns the hour of time (ranging from 0 to 23)
Mysql> select HOUR ('10: 05: 03 ');
-> 10

MINUTE (time)
Returns the number of minutes of time (ranging from 0 to 59)
Mysql> select MINUTE ('98-02-03 10:05:03 ');
-> 5

SECOND (time)
Returns the number of seconds (ranging from 0 to 59) of time)
Mysql> select SECOND ('10: 05: 03 ');
-> 3

PERIOD_ADD (P, N)
Add N months to period P and return (P format: YYMM or YYYYMM)
Mysql> select PERIOD_ADD (9801,2 );
-> 199803

PERIOD_DIFF (P1, P2)
Returns the number of months between period P1 and P2 (in the format of YYMM or YYYYMM for 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)
Addition and subtraction of date and time
(ADDDATE () and SUBDATE () are synonyms of DATE_ADD () and DATE_SUB (),

You can use operators + and-instead of functions.
Date is a DATETIME or DATE value. expr is a table that adds or subtracted from date.

The expression 'type' indicates how the expression 'expr' should be interpreted.
[Expected expr format]:
SECOND SECONDS
MINUTE MINUTES
HOUR time HOURS
DAY DAYS
MONTH-MONTH MONTHS
YEAR YEARS
MINUTE_SECOND MINUTES and SECONDS "MINUTES: SECONDS"
HOUR_MINUTE hour and minute "HOURS: MINUTES"
DAY_HOUR and hour "days hours"
YEAR_MONTH and month "YEARS-MONTHS"
HOUR_SECOND hour, minute, "HOURS: MINUTES: SECONDS"
DAY_MINUTE day, hour, minute "days hours: MINUTES"
DAY_SECOND day, hour, minute, second "DAYS

HOURS: MINUTES: SECONDS"
Expr allows any punctuation to be used as a separator. If all values are DATE values, the result is

DATE value, otherwise the result is a DATETIME Value)
If the type keyword is incomplete, MySQL takes the value from the right end. DAY_SECOND is missing

MINUTE_SECOND)
If you add MONTH, YEAR_MONTH, or YEAR, the number of days is greater than the maximum day of the result MONTH.

The maximum number of days)
Mysql> SELECT "23:59:59" + INTERVAL 1 SECOND;

-> 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 ("23:59:59", INTERVAL 1

SECOND );
-> 00:00:00
Mysql> SELECT DATE_ADD ("23:59:59", INTERVAL 1

DAY );
-> 23:59:59
Mysql> SELECT DATE_ADD ("23:59:59", INTERVAL

"1:1" MINUTE_SECOND );
-> 00:01:00
Mysql> SELECT DATE_SUB ("00:00:00", INTERVAL "1

"DAY_SECOND );
-> 1997-12-30 22:58:59
Mysql> SELECT DATE_ADD ("00:00:00", INTERVAL "-1

10 "DAY_HOUR );
-> 1997-12-30 14:00:00
Mysql> SELECT DATE_SUB ("1998-01-02", INTERVAL 31 DAY );
-> 1997-12-02
Mysql> select extract (year from "maid ");
-> 1999
Mysql> select extract (YEAR_MONTH FROM "maid

01:02:03 ");
-> 199907
Mysql> select extract (DAY_MINUTE FROM "2017-07-02

01:02:03 ");
-> 20102

TO_DAYS (date)
Returns the number of days since the date of the date (not calculated before January 1, 1582)
Mysql> select TO_DAYS (950501 );
-> 728779
Mysql> select TO_DAYS ('2017-10-07 ');
-> 729669

FROM_DAYS (N)
Returns the DATE value for the day from the DATE of the West dollar (not counted before January 1, 1582)
Mysql> select FROM_DAYS (729669 );
-> '2017-10-07'

DATE_FORMAT (date, format)
Format the date value based on the format string
(The flag is available in the format string:
% M month name (January ...... December)
% W name of the week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H Hour (00 ...... 23)
% K hour (0 ...... 23)
% H Hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, number (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 a week (0 = Sunday ...... 6 = Saturday)
% U week (0 ...... 52). Sunday is the first day of the week.
% U week (0 ...... 52) Monday is the first day of the week.
% Characters %)
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % W % M %

Y ');
-> 'Saturday October 1997'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H: % I: %

S ');
-> '22: 23: 00'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % D % y %

% D % m % B % J ');
-> '4th 97 Sat 04 10 Oct 123'
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H % k % I

% R % T % S % W ');
-> '22 22 10 10:23:00 PM 22:23:00 6'

TIME_FORMAT (time, format)
Similar to DATE_FORMAT (), but TIME_FORMAT only processes hours, minutes, and seconds.

The remainder sign generates a NULL value or 0)

CURDATE ()
CURRENT_DATE ()
Returns the current date value in the format of 'yyyy-MM-DD 'or 'yyyymmdd (based on

The context is a string or number)
Mysql> select CURDATE ();
-> '2017-12-15'
Mysql> select CURDATE () + 0;
-> 19971215

CURTIME ()
CURRENT_TIME ()
Returns the current time value in 'hh: MM: SS' or HHMMSS format (based on

The following is a string or number)
Mysql> select CURTIME ();
-> '23: 50: 26'
Mysql> select CURTIME () + 0;
-> 235026

NOW ()
SYSDATE ()
CURRENT_TIMESTAMP ()
Returns the current date in 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS format

Time (the context of the returned value is a string or number)
Mysql> select NOW ();
-> '2017-12-15 23:50:26'
Mysql> select NOW () + 0;
-> 19971215235026

UNIX_TIMESTAMP ()
UNIX_TIMESTAMP (date)
Returns a Unix timestamp (seconds starting from '2017-01-01 00:00:00 'GMT

Number, the default value of date is the current time)
Mysql> select UNIX_TIMESTAMP ();
-> 882226357
Mysql> select UNIX_TIMESTAMP ('2017-10-04 22:23:00 ');
-> 875996580

FROM_UNIXTIME (unix_timestamp)
Returns the timestamp in the format of 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS

Value (the context of the returned value is a string or number)
Mysql> select FROM_UNIXTIME (875996580 );
-> '2017-10-04 22:23:00'
Mysql> select FROM_UNIXTIME (875996580) + 0;
-> 19971004222300

FROM_UNIXTIME (unix_timestamp, format)
Returns the timestamp value in format.
Mysql> select FROM_UNIXTIME (UNIX_TIMESTAMP (), '% Y % D % M %

H: % I: % s % X ');
-> '2014 23rd December 03:43:30 x'

SEC_TO_TIME (seconds)
Returns the TIME value in the format of 'hh: MM: SS' or HHMMSS in seconds.

The context is a string or number)
Mysql> select SEC_TO_TIME (2378 );
-> '00: 39: 38'
Mysql> select SEC_TO_TIME (2378) + 0;
-> 3938

TIME_TO_SEC (time)
How many seconds does the returned time value have?
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) [whether the conversion can be successful or not depends on the data type during 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;

BitsCN.com