Solving Newlines in MySQL TO_BASE64()

How to remove unwanted line breaks in Base64 output

When working with binary data in MySQL- such as certificates, keys, or other BLOB content-the TO_BASE64() function is the easiest way to export binary values in a portable text format.
But many developers run into an annoying detail:

MySQL automatically inserts newlines into the Base64 output.

This can break APIs, JSON output, or scripts that expect Base64 to be a single continuous line. In this article, we’ll look at why this happens and how to reliably remove these unwanted newlines.

Why MySQL inserts newlines in TO_BASE64()

MySQL follows the MIME Base-64 specification RFC-2045. This standard requires that each encoded line must be no longer than 76 characters. Because of this, MySQL inserts a newline every 76 characters in the Base64-encoded string.

This made sense historically, e.g. for email compatibility, but in modern applications it often creates more problems than it solves.

The problem in practice

Example:

SELECT TO_BASE64(certificate)
FROM distributor
WHERE name = 'ACME';

Instead of one long Base64 line, the output may look like:

MIID5TCCA8+gAwIBAgIQB3h1g/...
ui83iFkV103ef28hiUIgUGuiA/...
g93kP6a1Dq7rzsQ==

Those newlines are part of the actual string—not just formatting in the client.

The solution: Remove newlines from Base64

MySQL does not offer an option like “NO LINE BREAKS”.
However, there’s a simple and reliable alternative solution:

SELECT REPLACE(REPLACE(TO_BASE64(certificate), '\n', ''), '\r', '')
FROM distributor
WHERE name = 'ACME';

This reliably strips out all MySQL-inserted line breaks and returns a clean, single-line Base64 value.

What makes the solution great?

It

• works in all MySQL versions
• is fast and lightweight (REPLACE() is very efficient)
• ensures that all MIME-style breaks are removed
• preserves the original BLOB unchanged
• works equally with Unix and windows newlines

These points make it a nice workaround that can be used in scripts, backends, and export pipelines.

Conclusion

If you export binary data like certificates from MySQL, you will eventually run into that nasty issue, but don’t be afraid,
the fix is simple:

Strip newlines using REPLACE() and you’re good to go.

You get a clean, single-line Base64 string that plays nicely with JSON, APIs, and configuration files.

So long, thanks for reading and have a great day.

I got your mouth wet for more certificate fun and SSL? This post is about setting up HTTPS for Maven. And adventure by itself. Or if you’d rather like to see something different? Here’s some testautomation fun with Rust.