MySQL Function References

Q

Where to find reference information and tutorials on MySQL database functions? I want to know how to use SUBSTRING(), ABS(), DATEDIFF(), and other MySQL functions.

✍: FYIcenter.com*

A

Here is a collection of reference information and tutorials on MySQL database functions compiled by FYIcenter.com DBA team.

Calling MySQL Built-in Functions

Function Name Case Insensitive

Function Argument Modifier

Variable Number of Arguments

Argument Type Auto-Conversion

Aggregate Functions

MySQL Functions on Character String Values

ASCII() - ASCII Value of First Character

BIT_LENGTH() - Number of Bits in String

CHAR() - Building String from Byte Values

CHAR_LENGTH() - Number of Characters

CHARACTER_LENGTH() - Synonym for CHAR_LENGTH()

CHARSET() - Detecting Character Set Name

COERCIBILITY() - Character Collation Coercibility

COLLATION() - Detecting Character Collation Name

CONCAT() - Concatenating Strings

CONCAT_WS() - Concatenation with Separator

CONV() - Binary/HEX String and Integer Conversion

ELT() - String at Given Index

FIELD() - Find String in String List

FIND_IN_SET() - Find Substring in List String

FROM_BASE64() - Base64 Decoding

HEX() - Calculate HEX representation

INSERT() - Insert and Replace Substring

INSTR() - Find Substring

LCASE() - Synonym for LOWER()

LEFT() - First N Characters

LENGTH() - Number of Bytes in String

LOCATE() - Locate Substring Starting at N

LOWER() - Convert String to Lower Case

LPAD() - Left-Padding String

LTRIM() - Left-Trimming String

MID() - Synonym for SUBSTRING()

OCT() - Calculate Octal representation

OCTET_LENGTH() - Synonym for LENGTH()

ORD() - Order Value of First Character

POSITION() - Synonym for LOCATE()

QUOTE() - MySQL Quoting String

REPEAT() - Repeating String

REPLACE() - Replacing Substring

REVERSE() - Reversing String

RIGHT() - Last N Characters

RPAD() - Right-Padding String

RTRIM() - Right-Trimming String

SOUNDEX() - Soundex Encoding

SPACE() - Repeating Space Character

STRCMP() - Comparing Strings

SUBSTR() - Synonym for SUBSTRING()

SUBSTRING() - Substring at Given Position

SUBSTRING_INDEX() - Substring in Delimited String

TO_BASE64() - Base64 Encoding

TRIM() - Trimming String

UCASE() - Synonym for UPPER()

UNHEX() - HEX to String Conversion

UPPER() - Convert String to Upper Case

WEIGHT_STRING() - String Sorting Weight

MySQL Functions on Numeric Values

ABS() - Absolute Value

ACOS() - Arccosine Trigonometric Value

ASIN() - Arcsine Trigonometric Value

ATAN() - Arctangent Trigonometric Value

ATAN2() - 2-Argument Arctangent

BIN() - Converting Integer to Binary String

BIT_COUNT() - Counting '1' in Binary String

CEIL() - Synonym for CEILING()

CEILING() - Rounding up to Integer

CONV() - Number Base Conversion

COS() - Cosine Trigonometric Value

COT() - Cotangent Trigonometric Value

DEGREES() - Radians to Degrees

FLOOR() - Flooring a Numeric Value

FORMAT() - Formatting Number to String

FORMAT_BYTES() - Formatting Bytes in Readable Units

EXP() - Exponential Based on E

EXPORT_SET() - Exporting Binary Set to On/Off Flags

INTERVAL() - Interval Position of Sorted List

LN() - Natural Logarithm

LOG() - Logarithm of Y to Base X

LOG10() - Logarithm to Base 10

LOG2() - Logarithm to Base 2

MAKE_SET() - Filtering List with Binary Set

MOD() - Remainder of X divided by Y

PI() - Value of PI, π

POW() - X Raised to Power of Y

POWER() - Synonym for POW()

RADIANS() - Degrees to Radians

RAND() - Random Value Generator

ROUND() - Rounding to Decimal Place

SIGN() - Sign of a Number

SIN() - Sine Trigonometric Value

SQRT() - Square Root of X

TAN() - Tangent Trigonometric Value

TRUNCATE() - Truncating to Decimal Place

MySQL Functions on Date and Time

ADDDATE() - Synonym for DATE_ADD()

ADDTIME() - Adding Time

CONVERT_TZ() - Converting Time Zone

CURDATE() - Current Date

CURRENT_DATE() - Synonym for CURDATE()

CURRENT_TIME() - Synonym for CURTIME()

CURRENT_TIMESTAMP() - Synonym for NOW()

CURTIME() - Current Time

DATE() - Extracting Date

DATEDIFF() - Difference of Two Dates

DATE_ADD() - Adding Date/Time Interval

DATE_FORMAT() - Formatting Date

DATE_SUB() - Decrementing Date/Time

DAY() - Synonym for DAYOFMONTH()

DAYNAME() - Name of Weekday

DAYOFMONTH() - Day of Month

DAYOFWEEK() - Day of Week (1=Sunday)

DAYOFYEAR() - Day of Year

EXTRACT() - Extracting Datetime Component

FORMAT_PICO_TIME() - Formatting Picoseconds in Readable Units

FROM_DAYS() - Converting Days to Date

FROM_UNIXTIME() - Unix Time to Datetime

GET_FORMAT() - Standard Datetime Formats

HOUR() - Hours of Time

LAST_DAY() - Last Day of Month

LOCALTIME() - Synonym for NOW()

LOCALTIMESTAMP() - Synonym for NOW()

MAKEDATE() - Making Date from Days

MAKETIME() - Making Time from Components

MICROSECOND() - Microseconds of Time

MINUTE() - Minutes of Time

MONTH() - Month of Year

MONTHNAME() - Name of Month

NOW() - Start Time of Execution

PERIOD_ADD() - Adding Months to Period

PERIOD_DIFF() - Difference of Year-Month Periods

QUARTER() - Quarter of Year

SECOND() - Seconds of Time

SEC_TO_TIME() - Converting Seconds to Time

STR_TO_DATE() - Parsing String to Datetime

SUBDATE() - Synonym for DATE_SUB()

SUBTIME() - Subtracting Time

SYSDATE() - Current Timestamp

TIME() - Extracting Time

TIMEDIFF() - Difference of Two Timestamps

TIMESTAMP() - Adding Time to Timestamp

TIMESTAMPADD() - Incrementing Timestamp

TIMESTAMPDIFF() - Difference in Timestamp Component

TIME_FORMAT() - Formatting Time

TIME_TO_SEC() - Converting Time to Seconds

TO_DAYS() - Converting Date to Days

TO_SECONDS() - Converting Datetime to seconds

UNIX_TIMESTAMP() - Calculating Unix Timestamp

UTC_DATE() - Current UTC Date

UTC_TIME() - Current UTC Time

UTC_TIMESTAMP() - Current UTC Timestamp

WEEK() - Week of Year

WEEKDAY() - Weekday (0=Monday)

WEEKOFYEAR() - Calendar Week of Year

YEAR() - Year of Date

YEARWEEK() - Year Week Combination

MySQL Functions on Aggregation Groups

ANY_VALUE() - Any Value in Group

AVG() - Average Value in Group

BIT_AND() - Bitwise AND in Group

BIT_OR() - Bitwise OR in Group

BIT_XOR() - Bitwise XOR in Group

COUNT() - Counting Items in Group

GROUP_CONCAT() - Concatenating Items in Group

GROUPING() - Identifying Super-Aggregate Row

JSON_ARRAYAGG() - Building JSON Array in Group

JSON_OBJECTAGG() - Building JSON Object in Group

MAX() - Maximum Value in Group

MIN() - Minimum Value in Group

STD() - Synonym for STDDEV_POP()

STDDEV() - Synonym for STDDEV_POP()

STDDEV_POP() - Population Standard Deviation

STDDEV_SAMP() - Sample Standard Deviation

SUM() - Total Value in Group

VAR_POP() - Population Standard Variance

VAR_SAMP() - Sample Standard Variance

VARIANCE() - Synonym for VAR_POP()

Window Functions

MySQL Functions on Result Set Windows

CUME_DIST() - Cumulative Distribution of Sorted Values

DENSE_RANK() - Density Rank of Sorted Values

FIRST_VALUE() - First Value of Result Set Window

LAG() - N-Row before Current Row within Window

LAST_VALUE() - Last Value of Result Set Window

LEAD() - N-Row after Current Row within Window

NTH_VALUE() - N-th Value of Result Set Window

NTILE() - Dividing Window into N Tiles

PERCENT_RANK() - Rank Percentage of Sorted Values

RANK() - Vale Rank of Sorted Values

ROW_NUMBER() - Row Position in Result Set Window

MySQL Functions on JSON Values

JSON_ARRAY() - Creating JSON Array

JSON_ARRAY_APPEND() - Appending Value to JSON Array

JSON_ARRAY_INSERT() - Inserting Value to JSON Array

JSON_CONTAINS() - Finding JSON in JSON

JSON_CONTAINS_PATH() - Finding Path in JSON

JSON_DEPTH() - Calculating Depth of JSON Value

JSON_EXTRACT() - Exacting Members from JSON

JSON_STORAGE_FREE() - Free Space in JSON Value

JSON_INSERT() - Inserting Members to JSON Value

JSON_KEYS() - Exacting Keys from JSON Object

JSON_LENGTH() - Calculating Length of JSON Value

JSON_MERGE() - Synonym for JSON_MERGE_PRESERVE()

JSON_MERGE_PATCH() - Merging JSON by Replacing Members

JSON_MERGE_PRESERVE() - Merging JSON with All Members

JSON_OBJECT() - Creating JSON Object

JSON_OVERLAPS() - Checking JSON Overlaps

JSON_PRETTY() - Validating JSON Value

JSON_QUOTE() - Quoting JSON String

JSON_REMOVE() - Removing JSON Child Members

JSON_REPLACE() - Replacing JSON Child Members

JSON_SCHEMA_VALID() - JSON Schema Validation

JSON_SCHEMA_VALIDATION_REPORT() - JSON Schema Validation Report

JSON_SEARCH() - Searching String in JSON

JSON_SET() - Inserting/updating JSON Child Members

JSON_STORAGE_SIZE() - Storage Size of JSON Value

JSON_TABLE() - Inline Table with JSON Array

JSON_TYPE() - Detecting Type of JSON Value

JSON_UNQUOTE() - Unquoting JSON String

JSON_VALID() - Validating JSON Value

JSON_VALUE() - Converting JSON Value

MySQL Functions for Encryption and Compression

AES_DECRYPT() - AES Data Decryption

AES_ENCRYPT() - AES Data Encryption

COMPRESS() - Compressing Data

CRC32() - Cyclic Redundancy Check 32-Bit

MD5() - Calculating MD5 Hash

RANDOM_BYTES() - Generating Random Bytes

SHA() - Synonym for SHA1()

SHA1() - Calculating SHA-1 Hash

SHA2() - Calculating SHA-2 Hash

STATEMENT_DIGEST() - Statement Digest Hash

STATEMENT_DIGEST_TEXT() - Calculating Statement Digest

UNCOMPRESS() - Uncompressing Data

UNCOMPRESSED_LENGTH() - Uncompressed Data Length

VALIDATE_PASSWORD_STRENGTH() - Password Strength Test

MySQL Functions on UUID Values

BIN_TO_UUID() - Converting Binary to UUID

IS_UUID() - Validating UUID String Format

UUID() - Generating UUID String

UUID_SHORT() - Short 64-Bit UUID Integer

UUID_TO_BIN() - Converting UUID to Binary

MySQL Functions on System Information

BENCHMARK() - Repeating Expression Evaluation

CONNECTION_ID() - Thread ID of Current Connection

CURRENT_ROLE() - Current Role of Logged-In User

CURRENT_USER() - Authentication Name of Current User

DATABASE() - Name of Current Database

FOUND_ROWS() - Row Count from Last SELECT Statement

ICU_VERSION() - ICU (International Components for Unicode) Version

LAST_INSERT_ID() - Last Value of AUTO_INCREMENT Column

PS_CURRENT_THREAD_ID() - PS Thread ID of Current Connect

PS_THREAD_ID() - PS Thread ID of Given Connect

ROLES_GRAPHML() - User Role Graph in GraphML Format

ROW_COUNT() - Affected Rows from Last DML Statement

SCHEMA() - Synonym for DATABASE()

SESSION_USER() - Synonym for USER()

SYSTEM_USER() - Synonym for USER()

USER() - Login Name of Current User

VERSION() - Version Number of MySQL Server

MySQL Functions for Miscellaneous Purposes

CAST() - Casting Expression to Value

COALESCE() - Finding First Non-NULL Value

CONVERT() - Character Set Conversion

DEFAULT() - Table Column Default Value

EXTRACTVALUE() - Extracting Text Content from XML

GET_LOCK() - Requesting User Defined Lock

GREATEST() - Finding the Greatest/Maximum Value

IF() - Conditional Value Selection

IFNULL() - Replacing NULL Value

INET_ATON() - IP Address String to Number

INET_NTOA() - IP Address Number to String

INET6_ATON() - IPv6 Address String to Number

INET6_NTOA() - IPv6 Address Number to String

IS_FREE_LOCK() - Checking Lock Status

IS_IPV4() - Detecting IPv4 Address

IS_IPV4_COMPAT() - IPv4-Compatible IPv6 Address

IS_IPV4_MAPPED() - IPv4-Mapped IPv6 Address

IS_IPV6() - Detecting IPv6 Address

IS_USED_LOCK() - Checking Lock Owner

ISNULL() - Detecting NULL Value

LEAST() - Finding the Least/Minimum Value

NAME_CONST() - PS Thread ID of Given Connect

NULLIF() - NULL on Equal Values

RELEASE_ALL_LOCKS() - Release All Locks

RELEASE_LOCK() - Release Lock Instance

SLEEP() - Holding Statement Execution

UPDATEXML() - Updating Child Element in XML

VALUES() - Column Value for "ON DUPLICATE KEY UPDATE"

Removed MySQL Functions

Removed: ENCODE() - Encoding Data with Password

Removed: DECODE() - Decoding Data with Password

Removed: DES_DECRYPT() - DES Data Decryption

Removed: DES_ENCRYPT() - DES Data Encryption

Removed: ENCRYPT() - Unix crypt() Hash Algorithm

Removed: JSON_APPEND() - Synonym for JSON_ARRAY_APPEND()

Removed: PASSWORD() - Generating Password Hash

⇒ Calling MySQL Built-in Functions

2023-11-18, 1167🔥, 0💬