MySQL learning notes (6): common functions


In this paper, the operating system is updated to MySQL 7.06-29.5 in 2019.

  • Numerical function
  • Date time function
  • String function
  • Process function
  • Aggregate function
  • Other functions
  • global variable

Numerical function

function effect
ABS(x) absolute value
CEIL(x) Round up
FLOOR(x) Round down
MOD(x, y) Remainder, equal to X% Y
RAND() Random number of interval [0,1]
ROUND(x[, n]) Round to N decimal places, n is 0 by default
TRUNCATE(x, n) Truncated to N decimal places

Date time function

function effect
CURDATE() current date
CURTIME() current time
DATE_ADD(t, INTERVAL expr type) Increase the time interval
DATEDIFF(later, previous) Later minus previous, regardless of time
DATE_FORMAT(t, fmt) Format with FMT
FROM_UNIXTIME(unix) Date time of UNIX timestamp
HOUR(t) hour
MINUTE(t) minute
MONTHNAME(t) Month name
NOW() Current date time
UNIX_TIMESTAMP(t) Convert to UNIX timestamp
WEEK(t) What is the week of the year
YEAR(t) particular year

DATE_ADD(t, INTERVAL expr type)OftypeThe following interval types are available.exprUse the corresponding format, which allows for loose intervals (that is, any punctuation). Values in the format can be negative. If the whole format is a numeric value, you can use the numeric format directly, otherwise you should use the string format.

Interval type format
DAY_SECOND DD hh:mm:ss
HOUR_SECOND hh:mm:ss

DATE_FORMAT(t, fmt)OffmtThe format symbols that can be used for parameters are as follows:

Formant type Formatter explain
second %S/%s Two digit seconds (00,…, 59)
branch %i Two digit form of Fen (00,…, 59)
Time %H Two digit hour, 24-hour system (00,…, 23)
Time %h/%I Two digit hour, 12 hour system (01,…, 12)
Time %k Hour in digital form, 24-hour system (0,…, 23)
Time %l Hour in digital form, 12 hour system (1,…, 12)
Hours, minutes and seconds %T Two digit, 24-hour hours, minutes and seconds (HH: mm: SS)
Hours, minutes and seconds %r Two digit, 12 hour time, minute and second (HH: mm: SS AM / PM)
Morning and afternoon %p Am or PM
week %W English week (Sunday)
week %a Abbreviation: Week (sun)
week %w Week in digital form (0 = Sunday)
day %d Two digit day (01,…, 31)
day %e Days in digital form (1,…, 31)
day %D Day (1st) in English suffix form
month %M English month
month %b Abbreviation month (Jan)
month %m Two digit month (01,…, 12)
month %c Months in digital form (1,…, 12)
year %Y Four digit year
year %y Two digit year
%j Day of the year, in three digit form (001,…, 366)
%U What is the week of the year? Sunday is the first day of the week. The first Sunday is the beginning of the first week. It is in two digit form (00,…, 52)
%u What is the week of the year, Monday is the first day of the week, 01-01 is the beginning of the first week, two digit form (00,…, 52)
% %% Character%

String function

The subscript of the first character of the string is 1.

function effect
CONCAT(v[, …]) Connection string, V can also be numeric
INSERT(str, idx, len, substr) Replace the len characters of STR from IDX (inclusive) with substr
LEFT(str, len) Take the left len character
LENGTH(str) String length
LOWER(str) Convert to lowercase
LPAD(str, len, pad) Use pad to fill the left side of STR to len
LTRIM(str) Remove the left space
REPEAT(str, n) Repeat n times
REPLACE(str, old, new) Replace old with new
RIGHT(str, len) Take len characters on the right
RPAD(str, len, pad) Use pad to fill the right side of STR to len
RTRIM(str) Remove right space
STRCMP(str1, str2) STR1 < STR2 returns – 1, STR1 = STR2 returns 0, STR1 > STR2 returns 1
SUBSTRING(str, idx, len) Intercept STR from IDX (inclusive) to len
TRIM(str) Remove the spaces on both sides
UPPER(str) Convert to uppercase

Process function

function effect
IF(value, t, f) If value is true, it returns t; otherwise, it returns F
IFNULL(value, result) Return value if value is not null, otherwise return result
CASE WHEN expr1 THEN result1 [WHEN …] [ELSE default] END Returns the result according to the true and false of each expression
CASE value WHEN enum1 THEN result1 [WHEN …] [ELSE default] END Value returns the result according to each enumeration value

Note when using process functionsNULLWhat’s going on.

Aggregate function

function effect
BIT_AND(row) Place and place
BIT_OR(row) Bitwise OR

Other functions

Functions for database management:

function effect
CURRENT_USER() Current user
DATABASE() Current database name, same asSCHEMA
PASSWORD(str) Encrypted into a system password, length 41
SCHEMA() Current database name, same asDATABASE
USER() Current login user name
VERSION() Current MySQL version

Functions for auxiliary queries:

function effect
FOUND_ROWS() The number of rows returned by the last query
LAST_INSERT_ID() Finally, insert the self growing value used by the record. If multiple records are inserted at one time, the self growing value used by the first record is returned

Function for calculation:

function effect
BIN(value) Binary formal representation
CRC32(value) CRC32 value
HEX(value) Representation in hexadecimal form
INET_ATON(ip) IP conversion from dot decimal to network byte order integer
INET_NTOA(uint) IP conversion from network byte order integer to dot decimal
MD5(value) MD5 value
SHA1(value) SHA1 value

global variable

variable effect
CURRENT_TIMESTAMP Current timestamp