MySQL LENGTH() Function

The LENGTH() function in MySQL is used to determine the length of a string in bytes.

Syntax

LENGTH(string)

Parameters

Definition and Use

• Purpose: Calculates the length of a string in terms of bytes.
• Behavior:
  • For single-byte character sets like latin1, the result matches the number of characters in the string.
  • For multi-byte character sets like utf8, the result is greater than the number of characters, as each character may require multiple bytes.

Usage Examples

Example 1: Basic Usage

Find the byte length of the string "SQL Tutorial":

SELECT LENGTH("SQL Tutorial") AS LengthOfString;

Output:

Explanation: The string "SQL Tutorial" contains 12 bytes (1 byte per character in a single-byte character set).

Example 2: Length of Column Values

Calculate the byte length of text in the CustomerName column:

SELECT CustomerName, LENGTH(CustomerName) AS LengthOfName
FROM Customers;

Output (example data):

Example 3: Multi-Byte Characters

Find the length of a string containing multi-byte characters:

SELECT LENGTH("你好世界") AS LengthOfString;

Output:

Explanation: Each character in "你好世界" is 3 bytes in a utf8 character set, so the total byte length is 12.

Technical Details

• Availability: Available from MySQL 4.0 onwards.
• Return Type: Returns an integer representing the number of bytes.

Applications

1. Data Validation: Check if string data fits within a specific byte limit (e.g., for storage or transmission).
2. Performance Optimization: Measure string sizes in bytes for storage calculations.
3. Character Encoding: Validate or debug multi-byte character issues.

Related Functions

• CHAR_LENGTH(): Returns the length of a string in characters, not bytes.
• OCTET_LENGTH(): Synonym for LENGTH(); measures string length in bytes.

Key Notes

• The result of LENGTH() depends on the character set used. Use CHAR_LENGTH() if you need the length in terms of characters instead of bytes.
• Be cautious when working with multi-byte character sets like utf8mb4, as the byte length may differ significantly from the character count.

The LENGTH() function is a straightforward and essential tool for byte-level string measurement in MySQL, especially when dealing with varying character sets or storage constraints.