-- TEXT數(shù)據(jù)類型最大存儲容量
-- 最大長度：65,535字節(jié)（64KB）
-- 字符編碼：UTF-8（每個字符最多4字節(jié)）
-- 實際字符數(shù)：約16,383個UTF-8字符

-- 創(chuàng)建包含TEXT字段的表
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    summary VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看表結(jié)構(gòu)
DESCRIBE articles;

-- 插入TEXT數(shù)據(jù)
INSERT INTO articles (title, content) VALUES (
    'MySQL TEXT類型詳解',
    '這是一篇關(guān)于MySQL TEXT數(shù)據(jù)類型的詳細(xì)說明文章。TEXT類型可以存儲最大65,535字節(jié)的數(shù)據(jù)，適合存儲中等長度的文本內(nèi)容，如文章內(nèi)容、評論、描述等。在實際應(yīng)用中，TEXT類型是存儲可變長度文本數(shù)據(jù)的常用選擇。'
);

-- 查看插入的數(shù)據(jù)
SELECT id, title, LENGTH(content) as content_length, content 
FROM articles WHERE id = 1;

-- TEXT字段的存儲特點
-- 1. 變長存儲：只存儲實際數(shù)據(jù)長度
-- 2. 外部存儲：數(shù)據(jù)存儲在外部頁面
-- 3. 指針存儲：表中只存儲指向數(shù)據(jù)的指針

-- 查看表的存儲信息
SELECT 
    table_name,
    data_length,
    index_length,
    data_free
FROM information_schema.tables 
WHERE table_schema = 'mydb' 
AND table_name = 'articles';

-- TEXT字段查詢性能特點
-- 1. 全表掃描時性能較差
-- 2. 索引限制：不能創(chuàng)建前綴索引
-- 3. 排序和分組性能較低

-- 查詢TEXT字段（性能較慢）
SELECT * FROM articles WHERE content LIKE '%MySQL%';

-- 使用SUBSTRING優(yōu)化查詢
SELECT * FROM articles 
WHERE SUBSTRING(content, 1, 100) LIKE '%MySQL%';

-- LONGTEXT數(shù)據(jù)類型最大存儲容量
-- 最大長度：4,294,967,295字節(jié)（4GB）
-- 字符編碼：UTF-8（每個字符最多4字節(jié)）
-- 實際字符數(shù)：約1,073,741,823個UTF-8字符

-- 創(chuàng)建包含LONGTEXT字段的表
CREATE TABLE documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看表結(jié)構(gòu)
DESCRIBE documents;

-- 插入LONGTEXT數(shù)據(jù)
INSERT INTO documents (title, content, file_size) VALUES (
    '大型文檔示例',
    CONCAT(
        REPEAT('這是一個很長的文檔內(nèi)容，用于測試LONGTEXT數(shù)據(jù)類型的存儲能力。', 1000),
        'LONGTEXT類型可以存儲最大4GB的數(shù)據(jù)，適合存儲大型文檔、日志文件、XML數(shù)據(jù)等。'
    ),
    LENGTH(CONCAT(
        REPEAT('這是一個很長的文檔內(nèi)容，用于測試LONGTEXT數(shù)據(jù)類型的存儲能力。', 1000),
        'LONGTEXT類型可以存儲最大4GB的數(shù)據(jù)，適合存儲大型文檔、日志文件、XML數(shù)據(jù)等。'
    ))
);

-- 查看插入的數(shù)據(jù)
SELECT id, title, file_size, LEFT(content, 100) as content_preview 
FROM documents WHERE id = 1;

-- LONGTEXT字段的存儲特點
-- 1. 大對象存儲：使用LOB（Large Object）存儲
-- 2. 外部存儲：數(shù)據(jù)存儲在專門的LOB頁面
-- 3. 分段存儲：大文件可能被分割存儲

-- 查看表的存儲信息
SELECT 
    table_name,
    data_length,
    index_length,
    data_free,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables 
WHERE table_schema = 'mydb' 
AND table_name = 'documents';

-- LONGTEXT字段查詢性能特點
-- 1. 查詢性能比TEXT更慢
-- 2. 內(nèi)存使用更多
-- 3. 網(wǎng)絡(luò)傳輸開銷大

-- 查詢LONGTEXT字段（性能很慢）
SELECT * FROM documents WHERE content LIKE '%文檔%';

-- 使用SUBSTRING優(yōu)化查詢
SELECT id, title, SUBSTRING(content, 1, 200) as content_preview 
FROM documents WHERE SUBSTRING(content, 1, 1000) LIKE '%文檔%';

-- 創(chuàng)建測試表
CREATE TABLE performance_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    text_field TEXT,
    longtext_field LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入測試數(shù)據(jù)
INSERT INTO performance_test (text_field, longtext_field) VALUES (
    REPEAT('TEXT字段測試數(shù)據(jù)', 100),
    REPEAT('LONGTEXT字段測試數(shù)據(jù)', 10000)
);

-- 性能測試查詢
-- TEXT字段查詢
SELECT COUNT(*) FROM performance_test WHERE text_field LIKE '%測試%';

-- LONGTEXT字段查詢
SELECT COUNT(*) FROM performance_test WHERE longtext_field LIKE '%測試%';

-- 查看存儲空間使用
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024), 2) AS 'Size (KB)',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables 
WHERE table_schema = 'mydb' 
AND table_name IN ('performance_test');

-- TEXT適合的場景
-- 1. 文章內(nèi)容
CREATE TABLE blog_posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT,  -- 文章內(nèi)容，通常不超過64KB
    excerpt VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. 評論內(nèi)容
CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT,
    content TEXT,  -- 評論內(nèi)容
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. 產(chǎn)品描述
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,  -- 產(chǎn)品描述
    price DECIMAL(10,2),
    category_id INT
);

-- LONGTEXT適合的場景
-- 1. 大型文檔
CREATE TABLE documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT,  -- 大型文檔內(nèi)容
    file_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. 日志文件
CREATE TABLE system_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    log_level VARCHAR(20),
    message LONGTEXT,  -- 詳細(xì)的日志信息
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. XML/JSON數(shù)據(jù)
CREATE TABLE api_responses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    endpoint VARCHAR(255),
    response_data LONGTEXT,  -- 大型API響應(yīng)數(shù)據(jù)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 選擇TEXT的情況
-- 1. 數(shù)據(jù)長度通常不超過64KB
-- 2. 需要頻繁查詢和更新
-- 3. 對查詢性能要求較高

-- 示例：文章內(nèi)容
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT,  -- 文章內(nèi)容，通常不會超過64KB
    author_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_author_created (author_id, created_at)
);

-- 選擇LONGTEXT的情況
-- 1. 數(shù)據(jù)長度可能超過64KB
-- 2. 存儲大型文檔或日志
-- 3. 對存儲容量要求較高

-- 示例：文檔存儲
CREATE TABLE documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT,  -- 大型文檔內(nèi)容
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_title (title)  -- 只對標(biāo)題建立索引
);

-- 避免在TEXT/LONGTEXT字段上直接查詢
-- 不好的做法
SELECT * FROM articles WHERE content LIKE '%關(guān)鍵詞%';

-- 好的做法：使用SUBSTRING
SELECT * FROM articles 
WHERE SUBSTRING(content, 1, 1000) LIKE '%關(guān)鍵詞%';

-- 更好的做法：添加搜索字段
ALTER TABLE articles ADD COLUMN search_keywords VARCHAR(500);
UPDATE articles SET search_keywords = SUBSTRING(content, 1, 500);
CREATE INDEX idx_search_keywords ON articles(search_keywords);

-- 查詢優(yōu)化后的字段
SELECT * FROM articles WHERE search_keywords LIKE '%關(guān)鍵詞%';

-- 使用壓縮存儲
ALTER TABLE documents 
ROW_FORMAT=COMPRESSED 
KEY_BLOCK_SIZE=8;

-- 定期優(yōu)化表
OPTIMIZE TABLE documents;

-- 分析表統(tǒng)計信息
ANALYZE TABLE documents;

-- TEXT字段不能創(chuàng)建普通索引
-- 錯誤示例
CREATE INDEX idx_content ON articles(content);  -- 錯誤

-- 正確做法：使用前綴索引
CREATE INDEX idx_content_prefix ON articles(content(100));

-- 或者使用全文索引
CREATE FULLTEXT INDEX ft_content ON articles(content);

-- 全文搜索查詢
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('關(guān)鍵詞' IN NATURAL LANGUAGE MODE);

-- LONGTEXT字段通常不建立索引
-- 因為數(shù)據(jù)太大，索引效果不明顯

-- 替代方案：使用輔助字段
ALTER TABLE documents ADD COLUMN content_hash VARCHAR(64);
UPDATE documents SET content_hash = MD5(content);
CREATE INDEX idx_content_hash ON documents(content_hash);

-- 或者使用外部搜索系統(tǒng)
-- 如Elasticsearch、Solr等

-- 博客文章表
CREATE TABLE blog_posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    excerpt VARCHAR(500),  -- 文章摘要
    content TEXT,  -- 文章內(nèi)容，使用TEXT
    author_id INT NOT NULL,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_author_status (author_id, status),
    INDEX idx_published_at (published_at),
    INDEX idx_slug (slug),
    FULLTEXT INDEX ft_content (content)
);

-- 插入示例數(shù)據(jù)
INSERT INTO blog_posts (title, slug, excerpt, content, author_id, status) VALUES (
    'MySQL TEXT和LONGTEXT的區(qū)別',
    'mysql-text-longtext-difference',
    '詳細(xì)介紹MySQL中TEXT和LONGTEXT數(shù)據(jù)類型的區(qū)別和使用場景',
    '這是一篇關(guān)于MySQL TEXT和LONGTEXT數(shù)據(jù)類型的詳細(xì)文章。TEXT類型適合存儲中等長度的文本內(nèi)容，最大容量為64KB。而LONGTEXT類型適合存儲大型文檔，最大容量為4GB。在實際應(yīng)用中，需要根據(jù)數(shù)據(jù)長度和性能要求來選擇合適的類型。',
    1,
    'published'
);

-- 文檔表
CREATE TABLE documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    filename VARCHAR(255),
    content LONGTEXT,  -- 文檔內(nèi)容，使用LONGTEXT
    file_size BIGINT,
    mime_type VARCHAR(100),
    version INT DEFAULT 1,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_title (title),
    INDEX idx_created_by (created_by),
    INDEX idx_created_at (created_at)
);

-- 文檔版本表
CREATE TABLE document_versions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    document_id INT NOT NULL,
    version INT NOT NULL,
    content LONGTEXT,  -- 版本內(nèi)容
    file_size BIGINT,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (document_id) REFERENCES documents(id),
    UNIQUE KEY uk_doc_version (document_id, version)
);

-- 系統(tǒng)日志表
CREATE TABLE system_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    log_level ENUM('DEBUG', 'INFO', 'WARNING', 'ERROR', 'CRITICAL') NOT NULL,
    category VARCHAR(100),
    message LONGTEXT,  -- 詳細(xì)日志信息
    context JSON,  -- 上下文信息
    user_id INT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_level_category (log_level, category),
    INDEX idx_created_at (created_at),
    INDEX idx_user_id (user_id)
);

-- 插入日志示例
INSERT INTO system_logs (log_level, category, message, context, user_id, ip_address) VALUES (
    'ERROR',
    'database',
    '數(shù)據(jù)庫連接失?。篊onnection refused. 詳細(xì)錯誤信息：MySQL server has gone away. 嘗試重新連接...',
    '{"attempt": 3, "timeout": 30, "host": "localhost", "port": 3306}',
    123,
    '192.168.1.100'
);

-- 監(jiān)控TEXT/LONGTEXT字段查詢性能
-- 啟用慢查詢?nèi)罩?
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- 查看慢查詢
SELECT 
    sql_text,
    exec_count,
    avg_timer_wait/1000000000 as avg_time_sec,
    sum_timer_wait/1000000000 as total_time_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE sql_text LIKE '%TEXT%' OR sql_text LIKE '%LONGTEXT%'
ORDER BY avg_timer_wait DESC;

-- 監(jiān)控表存儲空間
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
    ROUND((data_length / 1024 / 1024), 2) AS 'Data Size (MB)',
    ROUND((index_length / 1024 / 1024), 2) AS 'Index Size (MB)',
    table_rows
FROM information_schema.tables 
WHERE table_schema = 'mydb' 
AND (table_name LIKE '%text%' OR table_name LIKE '%longtext%')
ORDER BY (data_length + index_length) DESC;

-- 1. 使用分頁查詢
SELECT id, title, LEFT(content, 200) as content_preview 
FROM articles 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 0;

-- 2. 使用緩存
-- 在應(yīng)用層緩存查詢結(jié)果
-- 使用Redis等緩存系統(tǒng)

-- 3. 使用搜索索引
-- 為TEXT字段創(chuàng)建全文索引
CREATE FULLTEXT INDEX ft_articles_content ON articles(content);

-- 全文搜索查詢
SELECT id, title, 
       MATCH(content) AGAINST('關(guān)鍵詞' IN NATURAL LANGUAGE MODE) as relevance
FROM articles 
WHERE MATCH(content) AGAINST('關(guān)鍵詞' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

-- 1. 定期優(yōu)化表
OPTIMIZE TABLE articles;
OPTIMIZE TABLE documents;

-- 2. 使用壓縮存儲
ALTER TABLE documents 
ROW_FORMAT=COMPRESSED 
KEY_BLOCK_SIZE=8;

-- 3. 分區(qū)表（適用于大表）
CREATE TABLE large_documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);