> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/database/platform-specific-features/mysql/data-types/use-case.md).

# Use Case

## UUID

A `BINARY(16)` column in MySQL stores **16 bytes** of binary data, typically used to store **UUIDs** in binary form (instead of as strings like `'550e8400-e29b-41d4-a716-446655440000'`), which saves space and improves performance.

{% hint style="info" %}
In **MySQL**, when we store UUIDs using `BINARY(16)` and insert them using `UNHEX()`, tools like **MySQL Workbench** will display them as `BLOB` (binary) because:

* MySQL Workbench **doesn’t interpret the binary data as a UUID string**.
* `BINARY(16)` is the correct and efficient way to store UUIDs in MySQL for performance, but the raw binary format isn’t human-readable.
  {% endhint %}

Here are **some example `BINARY(16)` values**, shown in **hex format** (what we'll see if we run `HEX(column)`):

| Binary Value (Hex)                 | Notes                        |
| ---------------------------------- | ---------------------------- |
| `550E8400E29B41D4A716446655440000` | A UUID stored as binary      |
| `00000000000000000000000000000001` | Just 16 bytes ending in `01` |
| `FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF` | All bits set to 1            |
| `AABBCCDDEEFF00112233445566778899` | Random sample                |
| `0102030405060708090A0B0C0D0E0F10` | Sequential bytes             |

#### How to Insert into a `BINARY(16)` Column

```sql
CREATE TABLE sample_binary (
    id BINARY(16)
);
```

**1. Using `UNHEX()` with a hex string:**

```sql
INSERT INTO sample_binary (id) VALUES (UNHEX('550E8400E29B41D4A716446655440000'));
```

**2. Using a UUID directly:**

```sql
-- Convert UUID to binary and insert
INSERT INTO sample_binary (id) VALUES (UNHEX(REPLACE('550e8400-e29b-41d4-a716-446655440000', '-', '')));
```

#### Syntax for `WHERE` with hex value

```sql
SELECT *
FROM segment.content
WHERE uuid = UNHEX('0190DE6CBB1674B880C5F0A552BEA151');
```

#### Reading from a `BINARY(16)` Column

Using Concatenation

To **read** and **convert back to UUID format**:

```sql

SELECT
  LOWER(CONCAT_WS('-',
    HEX(SUBSTRING(id, 1, 4)),
    HEX(SUBSTRING(id, 5, 2)),
    HEX(SUBSTRING(id, 7, 2)),
    HEX(SUBSTRING(id, 9, 2)),
    HEX(SUBSTRING(id, 11))
  )) AS uuid_string
FROM sample_binary;
```

Using `BIN_TO_UUID()` (MySQL 8.0.13+ only):

```sql
SELECT BIN_TO_UUID(uuid) AS readable_uuid FROM segment.content;
```

Output:`0190de6c-bb16-74b8-80c5-f0a552bea151`(properly formatted UUID)

#### Why Use `BINARY(16)` for UUIDs?

| Format       | Storage | Index Size | Readability | Best For           |
| ------------ | ------- | ---------- | ----------- | ------------------ |
| `CHAR(36)`   | 36 B    | 36 B       | Readable    | Simplicity         |
| `BINARY(16)` | 16 B    | 16 B       | Binary      | Performance, Index |

Use `BINARY(16)` for performance; use `CHAR(36)` only if readability in the DB itself is critical.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://www.pranaypourkar.co.in/the-programmers-guide/database/platform-specific-features/mysql/data-types/use-case.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.