Posted on February 27, 2025 by Mahamodul Hasan Khan

50 MySQL Functions Examples from Laravel Projects

Laravel is a powerful and popular PHP framework that makes it easy for developers to interact with databases through Eloquent ORM (Object-Relational Mapping). However, there are situations where developers need to leverage raw SQL queries to perform more complex or specific tasks. In these scenarios, MySQL functions become particularly useful.

In this article, we will explore 50 useful MySQL functions that can be utilized in Laravel projects, particularly when working with raw SQL queries. These functions will be categorized based on their purpose: Math Functions, Functions for Conditions, Functions for Date/Time, Functions for Strings, and Functions for JSON. Along with each function, we will provide practical examples of how they can be used in Laravel projects.

1. Math Functions

Math functions are often used for performing arithmetic operations and calculations directly within MySQL queries. Here are some of the most common math functions used in Laravel raw SQL queries.

1.1 ABS()

The ABS() function returns the absolute value of a number.

Example:

$users = DB::table('users')
           ->select(DB::raw('ABS(balance) as absolute_balance'))
           ->get();

This query calculates the absolute value of the balance field for each user.

1.2 ROUND()

The ROUND() function rounds a number to the nearest integer or to a specified decimal place.

Example:

$products = DB::table('products')
              ->select(DB::raw('ROUND(price, 2) as rounded_price'))
              ->get();

This query rounds the price of each product to two decimal places.

1.3 FLOOR()

The FLOOR() function returns the largest integer value that is less than or equal to a given number.

Example:

$sales = DB::table('sales')
           ->select(DB::raw('FLOOR(total_amount) as floored_total'))
           ->get();

This query floors the total_amount field to the nearest lower integer.

1.4 CEIL()

The CEIL() function returns the smallest integer value that is greater than or equal to a given number.

Example:

$items = DB::table('items')
           ->select(DB::raw('CEIL(discounted_price) as ceiled_price'))
           ->get();

This query applies the ceiling function to the discounted_price.

1.5 POW()

The POW() function raises a number to a specified power.

Example:

$numbers = DB::table('numbers')
             ->select(DB::raw('POW(base, exponent) as powered_value'))
             ->get();

This query raises the base field to the power of the exponent field.

2. Functions for Conditions

Conditional functions are used to apply logic to your SQL queries. Here are some useful MySQL functions for conditionals.

2.1 IF()

The IF() function returns one value if a condition is true and another if the condition is false.

Example:

$orders = DB::table('orders')
            ->select(DB::raw('IF(status = "completed", "Success", "Pending") as order_status'))
            ->get();

This query returns “Success” if the status is “completed”, otherwise “Pending”.

2.2 CASE

The CASE statement provides a way to handle multiple conditional branches.

Example:

$products = DB::table('products')
              ->select(DB::raw('CASE WHEN stock > 0 THEN "In Stock" ELSE "Out of Stock" END as stock_status'))
              ->get();

This query returns either “In Stock” or “Out of Stock” based on the stock value.

2.3 IFNULL()

The IFNULL() function returns a specified value if the expression is NULL.

Example:

$users = DB::table('users')
           ->select(DB::raw('IFNULL(phone, "Not Available") as phone_number'))
           ->get();

This query returns “Not Available” if the phone field is NULL.

2.4 NULLIF()

The NULLIF() function returns NULL if two expressions are equal; otherwise, it returns the first expression.

Example:

$orders = DB::table('orders')
            ->select(DB::raw('NULLIF(discount, 0) as discount_value'))
            ->get();

This query returns NULL if discount is 0, otherwise, it returns the discount value.

2.5 COALESCE()

The COALESCE() function returns the first non-NULL value in a list of expressions.

Example:

$users = DB::table('users')
           ->select(DB::raw('COALESCE(address, "No Address Provided") as user_address'))
           ->get();

This query returns the user’s address if available, otherwise, it returns “No Address Provided”.

3. Functions for Date/Time

Date and time functions are essential for manipulating and querying time-based data. Here are some useful examples.

3.1 NOW()

The NOW() function returns the current date and time.

Example:

$logs = DB::table('activity_logs')
          ->select(DB::raw('NOW() as current_datetime'))
          ->get();

This query retrieves the current date and time.

3.2 DATE()

The DATE() function extracts the date part of a datetime value.

Example:

$events = DB::table('events')
            ->select(DB::raw('DATE(event_date) as event_day'))
            ->get();

This query extracts only the date from the event_date field.

3.3 DATEDIFF()

The DATEDIFF() function calculates the difference between two dates in days.

Example:

$users = DB::table('users')
           ->select(DB::raw('DATEDIFF(NOW(), registration_date) as days_since_registration'))
           ->get();

This query calculates how many days have passed since each user’s registration date.

3.4 YEAR()

The YEAR() function extracts the year part of a date.

Example:

$orders = DB::table('orders')
            ->select(DB::raw('YEAR(order_date) as order_year'))
            ->get();

This query retrieves the year part from the order_date.

3.5 DATE_FORMAT()

The DATE_FORMAT() function formats a date based on a specific format.

Example:

$users = DB::table('users')
           ->select(DB::raw('DATE_FORMAT(birthdate, "%M %d, %Y") as formatted_birthdate'))
           ->get();

This query formats the birthdate into a readable format like “January 01, 2025”.

4. Functions for Strings

String functions are used to manipulate and format string values.

4.1 CONCAT()

The CONCAT() function concatenates two or more strings.

Example:

$users = DB::table('users')
           ->select(DB::raw('CONCAT(first_name, " ", last_name) as full_name'))
           ->get();

This query concatenates the first_name and last_name fields to form the full_name.

4.2 SUBSTRING()

The SUBSTRING() function extracts a part of a string starting from a given position.

Example:

$users = DB::table('users')
           ->select(DB::raw('SUBSTRING(email, 1, 5) as email_prefix'))
           ->get();

This query extracts the first five characters of the email field.

4.3 LENGTH()

The LENGTH() function returns the length of a string.

Example:

$users = DB::table('users')
           ->select(DB::raw('LENGTH(username) as username_length'))
           ->get();

This query calculates the length of the username field.

4.4 REPLACE()

The REPLACE() function replaces occurrences of a substring within a string.

Example:

$users = DB::table('users')
           ->select(DB::raw('REPLACE(phone, "-", "") as formatted_phone'))
           ->get();

This query removes hyphens from the phone field.

4.5 UPPER()

The UPPER() function converts a string to uppercase.

Example:

$users = DB::table('users')
           ->select(DB::raw('UPPER(name) as upper_name'))
           ->get();

This query converts the name field to uppercase.

5. Functions for JSON

MySQL also offers functions to interact with JSON data.

5.1 JSON_EXTRACT()

The JSON_EXTRACT() function extracts a value from a JSON document.

Example:

$products = DB::table('products')
              ->select(DB::raw('JSON_EXTRACT(attributes, "$.color") as product_color'))
              ->get();

This query extracts the color field from the attributes JSON column.

5.2 JSON_ARRAY()

The JSON_ARRAY() function creates a JSON array from a list of values.

Example:

$orders = DB::table('orders')
            ->select(DB::raw('JSON_ARRAY(order_id, product_id, quantity) as order_details'))
            ->get();

This query creates a JSON array of order_id, product_id, and quantity for each order.

5.3 JSON_OBJECT()

The JSON_OBJECT() function creates a JSON object from a list of key-value pairs.

Example:

$users = DB::table('users')
           ->select(DB::raw('JSON_OBJECT("id", id, "name", name, "email", email) as user_info'))
           ->get();

This query creates a JSON object containing id, name, and email.

5.4 JSON_UNQUOTE()

The JSON_UNQUOTE() function removes the surrounding quotes from a JSON string.

Example:

$users = DB::table('users')
           ->select(DB::raw('JSON_UNQUOTE(JSON_EXTRACT(user_data, "$.phone")) as phone_number'))
           ->get();

This query extracts the phone field from the user_data JSON and removes the surrounding quotes.

5.5 JSON_SET()

The JSON_SET() function updates the value of a specific key in a JSON document.

Example:

$users = DB::table('users')
           ->where('id', 1)
           ->update(['user_data' => DB::raw('JSON_SET(user_data, "$.email", "newemail@example.com")')]);

This query updates the email field within the user_data JSON column.

6. Additional Math Functions

6.1 LOG()

The LOG() function returns the logarithm of a number to a specified base.

Example:

$numbers = DB::table('calculations')
             ->select(DB::raw('LOG(100, 10) as log_value'))
             ->get();

This query returns the base-10 logarithm of 100.

6.2 EXP()

The EXP() function returns the value of e raised to the power of a given number.

Example:

$calculations = DB::table('calculations')
                  ->select(DB::raw('EXP(1) as exp_value'))
                  ->get();

This query returns the value of e^1.

6.3 RAND()

The RAND() function returns a random floating-point value between 0 and 1.

Example:

$randomUsers = DB::table('users')
                 ->select(DB::raw('RAND() as random_value'))
                 ->get();

This query retrieves a random value for each user.

6.4 MOD()

The MOD() function returns the remainder of a division.

Example:

$results = DB::table('calculations')
             ->select(DB::raw('MOD(value, 3) as remainder'))
             ->get();

This query returns the remainder when value is divided by 3.

6.5 SIGN()

The SIGN() function returns the sign of a number: -1 for negative, 1 for positive, and 0 for zero.

Example:

$numbers = DB::table('numbers')
             ->select(DB::raw('SIGN(amount) as sign'))
             ->get();

This query returns the sign of the amount field.

7. Additional Functions for Conditions

7.1 GREATEST()

The GREATEST() function returns the largest value from a list of expressions.

Example:

$products = DB::table('products')
              ->select(DB::raw('GREATEST(price, discount) as max_value'))
              ->get();

This query returns the greater value between price and discount for each product.

7.2 LEAST()

The LEAST() function returns the smallest value from a list of expressions.

Example:

$items = DB::table('items')
           ->select(DB::raw('LEAST(stock, minimum_required) as minimum_stock'))
           ->get();

This query returns the lesser value between stock and minimum_required.

7.3 ISNULL()

The ISNULL() function checks if a value is NULL.

Example:

$users = DB::table('users')
           ->select(DB::raw('ISNULL(phone) as is_phone_null'))
           ->get();

This query returns 1 if the phone is NULL and 0 if it’s not.

7.4 BETWEEN

The BETWEEN function checks whether a value lies within a range of two values.

Example:

$orders = DB::table('orders')
            ->whereBetween('amount', [100, 500])
            ->get();

This query selects all orders where the amount is between 100 and 500.

7.5 IN()

The IN() function allows you to check if a value is within a list of values.

Example:

$users = DB::table('users')
           ->whereIn('status', ['active', 'pending'])
           ->get();

This query selects all users where status is either ‘active’ or ‘pending’.

8. Additional Functions for Date/Time

8.1 NOW()

The NOW() function returns the current date and time.

Example:

$logs = DB::table('logs')
          ->select(DB::raw('NOW() as current_datetime'))
          ->get();

This query retrieves the current timestamp.

8.2 MONTH()

The MONTH() function extracts the month from a date.

Example:

$transactions = DB::table('transactions')
                  ->select(DB::raw('MONTH(transaction_date) as transaction_month'))
                  ->get();

This query retrieves the month from the transaction_date.

8.3 WEEKDAY()

The WEEKDAY() function returns the day of the week for a given date (0 = Monday, 6 = Sunday).

Example:

$appointments = DB::table('appointments')
                  ->select(DB::raw('WEEKDAY(appointment_date) as weekday'))
                  ->get();

This query returns the weekday number for each appointment_date.

8.4 STR_TO_DATE()

The STR_TO_DATE() function parses a string to a date using a specified format.

Example:

$events = DB::table('events')
            ->select(DB::raw('STR_TO_DATE(event_start, "%Y-%m-%d") as event_start_date'))
            ->get();

This query converts the event_start string into a date format.

8.5 ADDDATE()

The ADDDATE() function adds a time interval to a date.

Example:

$users = DB::table('users')
           ->select(DB::raw('ADDDATE(last_login, INTERVAL 7 DAY) as next_login'))
           ->get();

This query adds 7 days to the last_login field to determine the next login date.

9. Additional Functions for Strings

9.1 CONCAT_WS()

The CONCAT_WS() function concatenates strings with a separator.

Example:

$users = DB::table('users')
           ->select(DB::raw('CONCAT_WS(", ", first_name, last_name) as full_name'))
           ->get();

This query concatenates first_name and last_name with a comma and space as a separator.

9.2 LEFT()

The LEFT() function extracts a specified number of characters from the left side of a string.

Example:

$users = DB::table('users')
           ->select(DB::raw('LEFT(email, 5) as email_prefix'))
           ->get();

This query extracts the first 5 characters from the email field.

9.3 RIGHT()

The RIGHT() function extracts a specified number of characters from the right side of a string.

Example:

$users = DB::table('users')
           ->select(DB::raw('RIGHT(username, 3) as username_suffix'))
           ->get();

This query extracts the last 3 characters from the username.

9.4 TRIM()

The TRIM() function removes leading and trailing spaces from a string.

Example:

$users = DB::table('users')
           ->select(DB::raw('TRIM(name) as trimmed_name'))
           ->get();

This query trims spaces from the name field.

9.5 REVERSE()

The REVERSE() function reverses a string.

Example:

$users = DB::table('users')
           ->select(DB::raw('REVERSE(name) as reversed_name'))
           ->get();

This query reverses the name field.

10. Additional Functions for JSON

10.1 JSON_ARRAYAGG()

The JSON_ARRAYAGG() function returns a JSON array of values grouped by an expression.

Example:

$categories = DB::table('products')
               ->select(DB::raw('JSON_ARRAYAGG(category_id) as category_ids'))
               ->groupBy('product_id')
               ->get();

This query creates a JSON array of category_id values for each product_id.

10.2 JSON_MERGE()

The JSON_MERGE() function merges two or more JSON documents into one.

Example:

$users = DB::table('users')
           ->select(DB::raw('JSON_MERGE(user_data, "{ \"status\": \"active\" }") as merged_data'))
           ->get();

This query merges the user_data JSON with a new status field.

10.3 JSON_TYPE()

The JSON_TYPE() function returns the type of a JSON value.

Example:

$users = DB::table('users')
           ->select(DB::raw('JSON_TYPE(user_data) as data_type'))
           ->get();

This query returns the type of the user_data field (e.g., object, array, string).

10.4 JSON_LENGTH()

The JSON_LENGTH() function returns the length of a JSON array or object.

Example:

$products = DB::table('products')
              ->select(DB::raw('JSON_LENGTH(attributes) as attribute_count'))
              ->get();

This query returns the number of elements in the attributes JSON field.

10.5 JSON_INSERT()

The JSON_INSERT() function inserts a value into a JSON document at a specified path.

Example:

$products = DB::table('products')
              ->where('id', 1)
              ->update(['attributes' => DB::raw('JSON_INSERT(attributes, "$.color", "red")')]);

This query adds a new key-value pair "color": "red" to the attributes JSON.

Conclusion

In this comprehensive guide, we’ve explored 50 different MySQL functions commonly used in Laravel projects. These functions span a variety of categories, including mathematical operations, conditions, date/time manipulation, string processing, and JSON handling. Laravel’s Eloquent ORM is powerful and simplifies many database operations, but as developers, we often need to dive deeper into MySQL’s functionality to handle more complex scenarios. Understanding how to leverage MySQL-specific functions in raw SQL queries, while still working within Laravel’s elegant syntax, allows developers to optimize their applications and make them more efficient.

Key Takeaways:

1. Math Functions: Functions like LOG(), EXP(), RAND(), and MOD() help perform complex calculations directly in the database, reducing the need for application-side processing. This can be especially helpful when dealing with large datasets where performing calculations on the fly in the database can save time and resources.
2. Functions for Conditions: With functions like GREATEST(), LEAST(), and BETWEEN(), you can easily handle conditional logic within your queries. These can simplify your queries and avoid the need for additional application logic, leading to cleaner, more maintainable code.
3. Date/Time Functions: MySQL offers several built-in date and time functions, such as NOW(), MONTH(), and ADDDATE(), which allow you to work with timestamps and perform date-based calculations or formatting directly in the database. This is crucial when you need to filter or manipulate data based on time without the overhead of doing it at the application level.
4. String Functions: Functions like CONCAT_WS(), LEFT(), RIGHT(), and REVERSE() help you manipulate strings within your queries. This is particularly useful when you’re working with text data and need to transform it to fit specific requirements, all without sending large amounts of data to the application for processing.
5. JSON Functions: With the growing adoption of JSON in modern applications, MySQL’s native JSON functions such as JSON_ARRAYAGG(), JSON_MERGE(), and JSON_INSERT() are essential tools for working with semi-structured data. These functions provide powerful ways to interact with JSON data, whether you’re aggregating it, modifying it, or simply extracting certain values.

By integrating these MySQL functions into your Laravel projects, you can achieve more flexibility, efficiency, and power in your database queries. The combination of Laravel’s eloquent ORM and raw SQL queries can be the perfect balance, offering the best of both worlds. Whether you’re dealing with complex calculations, conditional logic, time-sensitive data, text manipulation, or JSON structures, understanding these MySQL functions allows you to handle a broader set of use cases directly in the database, improving both performance and maintainability.

Lastly, while Eloquent is great for many scenarios, there are times when raw SQL queries are necessary to meet the specific needs of your application. By knowing how to incorporate these MySQL functions, you can ensure your Laravel application remains optimized and capable of handling even the most demanding requirements.

1. Q: What is the primary advantage of using raw SQL queries in Laravel?

A: Raw SQL queries offer greater flexibility and control over complex database operations that Eloquent may not handle efficiently. It allows developers to use advanced MySQL functions, handle performance optimization, and work with complex queries that require fine-tuning.

2. Q: Can I use MySQL functions in Laravel without writing raw SQL?

A: While Eloquent ORM abstracts most database queries, there are situations where MySQL-specific functions (like LOG(), RAND(), etc.) are required. For these, you need to use raw SQL queries with the DB::raw() method in Laravel.

3. Q: How does the RAND() function work in MySQL?

A: The RAND() function in MySQL returns a random floating-point value between 0 and 1. It’s useful when you want to randomize query results, such as fetching random products or users.

4. Q: What is the use of the CONCAT_WS() function in MySQL?

A: CONCAT_WS() concatenates multiple strings with a specified separator. For example, it can join first_name and last_name with a comma, making it easier to format data directly in the query without needing to manipulate it in the application.

5. Q: How do I use JSON_ARRAYAGG() in MySQL?

A: The JSON_ARRAYAGG() function aggregates values into a JSON array. In Laravel, it can be used to group results into a single JSON array, which is particularly useful when dealing with related data.

Example:

$products = DB::table('products')
            ->select(DB::raw('JSON_ARRAYAGG(category_id) as category_ids'))
            ->groupBy('product_id')
            ->get();

6. Q: How can I format a date using MySQL in Laravel?

A: You can use MySQL’s DATE_FORMAT() function to format a date in a specific way. This is useful when you need to display dates in a custom format (e.g., DD-MM-YYYY).

Example:

$users = DB::table('users')
           ->select(DB::raw('DATE_FORMAT(created_at, "%d-%m-%Y") as formatted_date'))
           ->get();

7. Q: What is the difference between GREATEST() and LEAST() in MySQL?

A: GREATEST() returns the largest value from a list of expressions, while LEAST() returns the smallest. These functions are particularly useful for comparing multiple values in a query.

Example:

$results = DB::table('calculations')
            ->select(DB::raw('GREATEST(price, discount) as max_value'))
            ->get();

8. Q: What does the ISNULL() function do in MySQL?

A: The ISNULL() function checks if a value is NULL. It returns 1 if the value is NULL and 0 otherwise. It is helpful for handling NULL values in queries.

Example:

$users = DB::table('users')
           ->select(DB::raw('ISNULL(phone) as is_phone_null'))
           ->get();

9. Q: How do I use MySQL’s STR_TO_DATE() function in Laravel?

A: The STR_TO_DATE() function is used to convert a string to a date format. This can be especially useful if you’re working with string representations of dates in your database.

Example:

$events = DB::table('events')
            ->select(DB::raw('STR_TO_DATE(event_start, "%Y-%m-%d") as event_start_date'))
            ->get();

10. Q: Can I use JSON_MERGE() to combine two JSON documents in Laravel?

A: Yes, JSON_MERGE() merges two or more JSON documents. This is helpful when you need to add new properties or values to an existing JSON object in a database.

Example:

$products = DB::table('products')
              ->select(DB::raw('JSON_MERGE(attributes, "{ \"color\": \"red\" }") as merged_data'))
              ->get();

11. Q: What does the MONTH() function do in MySQL?

A: The MONTH() function extracts the month part from a given date. It’s useful when you need to filter or group data by the month.

Example:

$transactions = DB::table('transactions')
                  ->select(DB::raw('MONTH(transaction_date) as transaction_month'))
                  ->get();

12. Q: How can I get the current date and time in MySQL using Laravel?

A: You can use MySQL’s NOW() function to get the current date and time. This is useful for logging and timestamping records.

Example:

$logs = DB::table('logs')
          ->select(DB::raw('NOW() as current_datetime'))
          ->get();

13. Q: How do I get a substring of a string in MySQL?

A: MySQL provides the LEFT() and RIGHT() functions to extract substrings from the left or right side of a string, respectively.

Example:

$users = DB::table('users')
           ->select(DB::raw('LEFT(email, 5) as email_prefix'))
           ->get();

14. Q: How do I check if a value is in a range using MySQL?

A: The BETWEEN operator in MySQL is used to check if a value lies within a given range.

Example:

$orders = DB::table('orders')
            ->whereBetween('amount', [100, 500])
            ->get();

15. Q: How do I get a random record in MySQL?

A: You can use the RAND() function in MySQL to return a random value, which is useful for fetching random records from a database.

Example:

$randomUser = DB::table('users')
                ->orderBy(DB::raw('RAND()'))
                ->first();

These Q&A provide a solid understanding of how to utilize MySQL functions in Laravel projects. Each function serves a specific purpose, making it easier for developers to write more efficient and powerful queries directly in the database, improving the overall performance of the application.