MySQL中TEXT和LONGTEXT区别举例详解

-- text数据类型最大存储容量
-- 最大长度：65,535字节（64kb）
-- 字符编码：utf-8（每个字符最多4字节）
-- 实际字符数：约16,383个utf-8字符

-- 创建包含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
);

-- 查看表结构
describe articles;

-- 插入text数据
insert into articles (title, content) values (
    'mysql text类型详解',
    '这是一篇关于mysql text数据类型的详细说明文章。text类型可以存储最大65,535字节的数据，适合存储中等长度的文本内容，如文章内容、评论、描述等。在实际应用中，text类型是存储可变长度文本数据的常用选择。'
);

-- 查看插入的数据
select id, title, length(content) as content_length, content 
from articles where id = 1;

-- text字段的存储特点
-- 1. 变长存储：只存储实际数据长度
-- 2. 外部存储：数据存储在外部页面
-- 3. 指针存储：表中只存储指向数据的指针

-- 查看表的存储信息
select 
    table_name,
    data_length,
    index_length,
    data_free
from information_schema.tables 
where table_schema = 'mydb' 
and table_name = 'articles';

-- text字段查询性能特点
-- 1. 全表扫描时性能较差
-- 2. 索引限制：不能创建前缀索引
-- 3. 排序和分组性能较低

-- 查询text字段（性能较慢）
select * from articles where content like '%mysql%';

-- 使用substring优化查询
select * from articles 
where substring(content, 1, 100) like '%mysql%';

-- longtext数据类型最大存储容量
-- 最大长度：4,294,967,295字节（4gb）
-- 字符编码：utf-8（每个字符最多4字节）
-- 实际字符数：约1,073,741,823个utf-8字符

-- 创建包含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
);

-- 查看表结构
describe documents;

-- 插入longtext数据
insert into documents (title, content, file_size) values (
    '大型文档示例',
    concat(
        repeat('这是一个很长的文档内容，用于测试longtext数据类型的存储能力。', 1000),
        'longtext类型可以存储最大4gb的数据，适合存储大型文档、日志文件、xml数据等。'
    ),
    length(concat(
        repeat('这是一个很长的文档内容，用于测试longtext数据类型的存储能力。', 1000),
        'longtext类型可以存储最大4gb的数据，适合存储大型文档、日志文件、xml数据等。'
    ))
);

-- 查看插入的数据
select id, title, file_size, left(content, 100) as content_preview 
from documents where id = 1;

-- longtext字段的存储特点
-- 1. 大对象存储：使用lob（large object）存储
-- 2. 外部存储：数据存储在专门的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. 内存使用更多
-- 3. 网络传输开销大

-- 查询longtext字段（性能很慢）
select * from documents where content like '%文档%';

-- 使用substring优化查询
select id, title, substring(content, 1, 200) as content_preview 
from documents where substring(content, 1, 1000) like '%文档%';

-- 创建测试表
create table performance_test (
    id int primary key auto_increment,
    text_field text,
    longtext_field longtext,
    created_at timestamp default current_timestamp
);

-- 插入测试数据
insert into performance_test (text_field, longtext_field) values (
    repeat('text字段测试数据', 100),
    repeat('longtext字段测试数据', 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. 文章内容
create table blog_posts (
    id int primary key auto_increment,
    title varchar(255) not null,
    content text,  -- 文章内容，通常不超过64kb
    excerpt varchar(500),
    created_at timestamp default current_timestamp
);

-- 2. 评论内容
create table comments (
    id int primary key auto_increment,
    post_id int,
    content text,  -- 评论内容
    user_id int,
    created_at timestamp default current_timestamp
);

-- 3. 产品描述
create table products (
    id int primary key auto_increment,
    name varchar(255) not null,
    description text,  -- 产品描述
    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,  -- 大型文档内容
    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,  -- 详细的日志信息
    timestamp timestamp default current_timestamp
);

-- 3. xml/json数据
create table api_responses (
    id int primary key auto_increment,
    endpoint varchar(255),
    response_data longtext,  -- 大型api响应数据
    created_at timestamp default current_timestamp
);

-- 选择text的情况
-- 1. 数据长度通常不超过64kb
-- 2. 需要频繁查询和更新
-- 3. 对查询性能要求较高

-- 示例：文章内容
create table articles (
    id int primary key auto_increment,
    title varchar(255) not null,
    content text,  -- 文章内容，通常不会超过64kb
    author_id int,
    created_at timestamp default current_timestamp,
    index idx_author_created (author_id, created_at)
);

-- 选择longtext的情况
-- 1. 数据长度可能超过64kb
-- 2. 存储大型文档或日志
-- 3. 对存储容量要求较高

-- 示例：文档存储
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,
    index idx_title (title)  -- 只对标题建立索引
);

-- 避免在text/longtext字段上直接查询
-- 不好的做法
select * from articles where content like '%关键词%';

-- 好的做法：使用substring
select * from articles 
where substring(content, 1, 1000) like '%关键词%';

-- 更好的做法：添加搜索字段
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);

-- 查询优化后的字段
select * from articles where search_keywords like '%关键词%';

-- 使用压缩存储
alter table documents 
row_format=compressed 
key_block_size=8;

-- 定期优化表
optimize table documents;

-- 分析表统计信息
analyze table documents;

-- text字段不能创建普通索引
-- 错误示例
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('关键词' in natural language mode);

-- longtext字段通常不建立索引
-- 因为数据太大，索引效果不明显

-- 替代方案：使用辅助字段
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);

-- 或者使用外部搜索系统
-- 如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,  -- 文章内容，使用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)
);

-- 插入示例数据
insert into blog_posts (title, slug, excerpt, content, author_id, status) values (
    'mysql text和longtext的区别',
    'mysql-text-longtext-difference',
    '详细介绍mysql中text和longtext数据类型的区别和使用场景',
    '这是一篇关于mysql text和longtext数据类型的详细文章。text类型适合存储中等长度的文本内容，最大容量为64kb。而longtext类型适合存储大型文档，最大容量为4gb。在实际应用中，需要根据数据长度和性能要求来选择合适的类型。',
    1,
    'published'
);

-- 文档表
create table documents (
    id int primary key auto_increment,
    title varchar(255) not null,
    filename varchar(255),
    content longtext,  -- 文档内容，使用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,  -- 版本内容
    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)
);

-- 系统日志表
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,  -- 详细日志信息
    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',
    '数据库连接失败：connection refused. 详细错误信息：mysql server has gone away. 尝试重新连接...',
    '{"attempt": 3, "timeout": 30, "host": "localhost", "port": 3306}',
    123,
    '192.168.1.100'
);

-- 监控text/longtext字段查询性能
-- 启用慢查询日志
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;

-- 监控表存储空间
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. 使用缓存
-- 在应用层缓存查询结果
-- 使用redis等缓存系统

-- 3. 使用搜索索引
-- 为text字段创建全文索引
create fulltext index ft_articles_content on articles(content);

-- 全文搜索查询
select id, title, 
       match(content) against('关键词' in natural language mode) as relevance
from articles 
where match(content) against('关键词' in natural language mode)
order by relevance desc;

-- 1. 定期优化表
optimize table articles;
optimize table documents;

-- 2. 使用压缩存储
alter table documents 
row_format=compressed 
key_block_size=8;

-- 3. 分区表（适用于大表）
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
);