MySQL - Useful Functions

MySQL - Useful Functions

A Guide to MySQL’s Built-in Functions

MySQL provides a rich library of built-in functions to perform a wide variety of operations on your data directly within SQL queries. Mastering these functions can significantly simplify your application logic, improve query performance, and allow for powerful data manipulation and analysis. This guide categorizes the most important functions.

String Functions

These functions are used to manipulate text data. Common tasks include concatenation, finding substrings, changing case, and trimming spaces.

• CONCAT(): Joins two or more strings together.
• LENGTH() / CHAR_LENGTH(): Returns the length of a string in bytes or characters.
• UPPER() / LOWER(): Converts a string to uppercase or lowercase.
• SUBSTRING(): Extracts a portion of a string.
• REPLACE(): Replaces all occurrences of a substring with another substring.
• TRIM(): Removes leading and trailing spaces.

Numeric Functions

These functions perform mathematical calculations on numeric data. They cover everything from basic arithmetic to more complex statistical operations.

• ROUND(): Rounds a number to a specified number of decimal places.
• CEIL() / FLOOR(): Rounds a number up or down to the nearest integer.
• ABS(): Returns the absolute (non-negative) value of a number.
• MOD(): Returns the remainder of a division operation.
• RAND(): Generates a random floating-point number between 0 and 1.

Date and Time Functions

Essential for working with temporal data, these functions allow you to manipulate and extract information from dates and times.

• NOW() / CURDATE() / CURTIME(): Returns the current date and time, just the date, or just the time.
• DATE_FORMAT(): Formats a date into a specified string format.
• DATEDIFF(): Calculates the number of days between two dates.
• DATE_ADD() / DATE_SUB(): Adds or subtracts a time interval from a date.
• YEAR() / MONTH() / DAY(): Extracts the year, month, or day part of a date.

Aggregate Functions

Aggregate functions perform a calculation on a set of rows and return a single summary value. They are almost always used with the GROUP BY clause.

• COUNT(): Counts the number of rows.
• SUM(): Calculates the sum of values in a column.
• AVG(): Calculates the average of values in a column.
• MIN() / MAX(): Finds the minimum or maximum value in a column.
• GROUP_CONCAT(): Concatenates strings from a group into a single string.

Control Flow Functions

These functions add conditional logic to your queries, allowing you to return different values based on certain conditions.

• IF(): A simple if-then-else function.
• CASE: A more powerful and flexible conditional statement, similar to a switch statement in programming.
• COALESCE(): Returns the first non-NULL value in a list.
• IFNULL() / NULLIF(): Specialized functions for handling NULL values.

Modern and Advanced Functions

Recent versions of MySQL have introduced powerful functions for handling complex data structures and performing advanced analytics.

• Window Functions (MySQL 8.0+): Functions like ROW_NUMBER(), RANK(), LEAD(), and LAG() that perform calculations across a set of table rows related to the current row. Essential for advanced analytics.
• JSON Functions (MySQL 5.7+): A suite of functions like JSON_OBJECT(), JSON_ARRAY(), JSON_EXTRACT(), and JSON_TABLE() for creating, parsing, and querying JSON data stored in your database.