Questions

11 of 30

1What are functions and operators in MySQL, and how are they different?

2What is the difference between single-row functions and aggregate functions?

3Give examples of commonly used string functions in MySQL.

4What is the use of the CONCAT() function? How is it different from using ||?

5Explain the difference between NOW(), CURDATE(), and SYSDATE().

6What are arithmetic operators in MySQL? Give examples.

7What is the difference between the = and <=> operators?

8What does the DISTINCT keyword do when used with aggregate functions like COUNT()?

9What is the difference between IFNULL() and COALESCE() functions?

10What are logical operators in MySQL, and how do AND, OR, and NOT work?

11What is the difference between LENGTH() and CHAR_LENGTH() functions?

12How does MySQL handle type conversion when using operators on different data types?

13What is the difference between ROUND(), TRUNCATE(), and FORMAT() functions?

14Explain how CASE and IF() functions can be used for conditional logic.

15What are comparison operators in MySQL, and how are they used with NULL values?

16How do aggregate functions like AVG(), SUM(), and MAX() behave when NULLs are present?

17Explain the use of REGEXP and LIKE operators. What’s the difference between them?

18What are user-defined functions (UDFs), and how do they differ from stored procedures?

19What are the differences between DATE_ADD() and ADDDATE() functions?

20How can you use STR_TO_DATE() and DATE_FORMAT() for converting and displaying date values?

21How does MySQL internally optimize and cache results of deterministic functions?

22What are window functions in MySQL 8.0, and how do they differ from aggregate functions?

23Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER() window functions.

24How does MySQL evaluate operator precedence when multiple operators are used in a single expression?

25Can functions be used in the WHERE clause? What are the performance implications?

26How can you use JSON_EXTRACT() and JSON_CONTAINS() to work with JSON data in MySQL?

27What are the performance trade-offs of using scalar functions inside JOIN or GROUP BY clauses?

28Explain how collation affects comparison operators in string functions.

29What are deterministic and non-deterministic functions? How does this affect replication and indexes?

30How would you combine multiple functions and operators to clean, transform, and aggregate data efficiently in one query?

mysql

Functions and Operators

11 / 30

What is the difference between LENGTH() and CHAR_LENGTH() functions?

Difference Between LENGTH() and CHAR_LENGTH() in MySQL

MySQL provides both LENGTH() and CHAR_LENGTH() functions to measure string size, but they work differently depending on bytes vs characters.

1. LENGTH()

1
Returns the number of bytes in a string.
2
Useful when dealing with multi-byte character sets like UTF-8.
3
Since some characters occupy more than 1 byte, LENGTH() may return a larger value.

Example of LENGTH()

2. CHAR_LENGTH() (or CHARACTER_LENGTH())

1
Returns the number of characters in a string.
2
Counts logical characters, regardless of how many bytes each uses.
3
Useful when validating input lengths for multilingual text.

Example of CHAR_LENGTH()

Key Difference

1
**LENGTH() → counts bytes
2
CHAR_LENGTH() → counts characters

In summary: Use LENGTH() when storage size matters, and CHAR_LENGTH() when the number of visible characters matters.

Difficulty: 5/10

Topics: string functions, multibyte handling, performance

Scenario Questions

0-2 years experience

1
You need to store user names and ensure they don't exceed 20 characters. How would you write a MySQL CHECK using LENGTH() vs CHAR_LENGTH() to enforce this for UTF8 names?
2
If you run SELECT LENGTH('café') and SELECT CHAR_LENGTH('café'), what values do you expect and why?

2-5 years experience

1
Your pagination query uses LENGTH(col) to limit text length, but you notice truncation occurs mid‑character for some languages. Walk me through how you'd debug and fix it.
2
A colleague replaced CHAR_LENGTH with LENGTH in a report that counts characters in product descriptions, and the numbers look off for emojis. Explain why and propose a fix.

5-8 years experience

1
Our service stores millions of multilingual comments and indexes a computed column based on string length for quick filtering. Discuss the trade‑offs of using LENGTH vs CHAR_LENGTH for that index, considering storage, performance, and correctness.
2
During a data migration we need to convert a VARCHAR column to a CHAR column with a fixed byte length. How does choosing LENGTH vs CHAR_LENGTH affect the migration script and potential data loss?

8+ years experience

1
The company is consolidating several legacy MySQL schemas that were built before UTF8mb4 support. Some use LENGTH to enforce field limits, others use CHAR_LENGTH. As a staff engineer, outline a migration strategy that ensures consistency, minimizes downtime, and addresses cross‑team concerns.
2
You are designing a shared library for string validation used across microservices written in different languages. How would you abstract the difference between byte length and character length to avoid similar bugs, and what governance would you put in place?

Follow-up Questions

❓What would happen if you used LENGTH on a UTF8 column to enforce a max‑character limit?
❓Can you think of any indexes or generated columns that would behave differently with these functions?
❓How does the choice affect query performance on large tables?