Store RTF in MySQL TEXT

by

Posted on by

RTF(Rich Text Format) can be stored as MySQL TEXT data.

1. MySQL TEXT Type Overview

MySQL offers multi-level TEXT types:

  • TINYTEXT (255B), TEXT (64KB), MEDIUMTEXT (16MB), LONGTEXT (4GB)

MEDIUMTEXT/LONGTEXT are recommended for RTF storage (covers most scenarios).

2. Pure SQL Implementation Steps

Step 1: Create Table with MEDIUMTEXT Field

CREATE TABLE RTF_DOCS (
    DOC_ID INT PRIMARY KEY AUTO_INCREMENT,
    DOC_NAME VARCHAR(100) NOT NULL,
    RTF_CONTENT MEDIUMTEXT NOT NULL, -- 16MB capacity (ideal for RTF)
    CREATE_TIME DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Unified UTF-8 encoding

Step 2: Insert RTF Text into TEXT Field

-- Method 1: Insert short RTF text directly
INSERT INTO RTF_DOCS (DOC_NAME, RTF_CONTENT)
VALUES (
    'test.rtf',
    '{\\rtf1\\ansi\\deff0 {\\fonttbl {\\f0 Calibri;}} \\pard\\f0\\fs22 MySQL RTF Test\\par}'
);

-- Method 2: Import external RTF file (MySQL command line)
-- 1. Convert RTF to UTF-8 first
-- 2. Import with the following commands (log in to MySQL client)
USE your_database;
SET @rtf_content = LOAD_FILE('/path/to/test.rtf'); -- File must be accessible to MySQL server
INSERT INTO RTF_DOCS (DOC_NAME, RTF_CONTENT) VALUES ('imported.rtf', @rtf_content);

Step 3: Query/Edit RTF Content

-- Query full RTF content
SELECT DOC_NAME, RTF_CONTENT FROM RTF_DOCS WHERE DOC_ID = 1;

-- Truncate RTF preview (first 200 characters)
SELECT DOC_NAME, SUBSTRING(RTF_CONTENT, 1, 200) AS RTF_PREVIEW FROM RTF_DOCS;

-- Update RTF content
UPDATE RTF_DOCS 
SET RTF_CONTENT = '{\\rtf1\\ansi\\deff0 \\pard\\f0\\fs30 Updated MySQL RTF\\par}'
WHERE DOC_ID = 1;

Step 4: Delete RTF Record

DELETE FROM RTF_DOCS WHERE DOC_ID = 1;

3. Simplify MySQL TEXT RTF Management with DBBlobEditor

DBBlobEditor addresses MySQL TEXT RTF management pain points:

4. Notes

  • LOAD_FILE() only reads files on the MySQL server; grant FILE permission first: `GRANT FILE ON *.* TO ‘user’@’localhost’;`
  • Adjust MySQL configuration `max_allowed_packet` (default 4MB) for large RTF inserts
  • MEDIUMTEXT (16MB) suffices for most RTF files; use LONGTEXT for extra-large files

Related Guides