MySQL数据类型之字符串、数字、日期详解
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">前言</a></li><li><a href="#_label1">1. 字符串（String）数据类型</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">1.1 定长与变长字符串</a></li><ul class="third_class_ul"><li><a href="#_label3_1_0_0">CHAR vs. VARCHAR</a></li></ul><li><a href="#_lab2_1_1">1.2 TEXT 和 BLOB</a></li><ul class="third_class_ul"><li><a href="#_label3_1_1_1">TEXT vs. VARCHAR</a></li></ul></ul><li><a href="#_label2">2. 数字（Numeric）数据类型</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_2">2.1 整数类型</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_2_3">2.2 浮点与定点类型</a></li><ul class="third_class_ul"><li><a href="#_label3_2_3_2">FLOAT vs. DECIMAL</a></li></ul><li><a href="#_lab2_2_4">3. 日期与时间（Date & Time）</a></li><ul class="third_class_ul"><li><a href="#_label3_2_4_3">DATETIME vs. TIMESTAMP</a></li></ul><li><a href="#_lab2_2_5">4. 选择数据类型的最佳实践</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label3">总结</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>前言</h2><p>在 MySQL 中，选择合适的数据类型对于数据库的<strong>存储效率</strong>和<strong>查询性能</strong>至关重要。MySQL 提供了**字符串（String）、数字（Numeric）和日期（Date & Time）**三大类数据类型，每种类型又有不同的子类型，以适应不同的业务需求。</p>
<p class="maodian"><a name="_label1"></a></p><h2>1. 字符串（String）数据类型</h2>
<p>字符串类型用于存储文本数据，主要分为<strong>定长（CHAR）<strong>和</strong>变长（VARCHAR）</strong>，以及<strong>大文本（TEXT 和 BLOB）</strong>。</p>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>1.1 定长与变长字符串</h3>
<table><thead><tr><th>数据类型</th><th>存储方式</th><th>特点</th><th>适用场景</th></tr></thead><tbody><tr><td><strong>CHAR(n)</strong></td><td>定长存储（不足补空格）</td><td>访问速度快，占用空间固定</td><td>适用于固定长度的数据，如国家代码、身份证号</td></tr><tr><td><strong>VARCHAR(n)</strong></td><td>变长存储（按需分配空间）</td><td>节省存储空间，但查询时可能需要额外的存储开销</td><td>适用于长度不固定的文本，如用户昵称、电子邮件</td></tr></tbody></table>
<p class="maodian"><a name="_label3_1_0_0"></a></p><h4>CHAR vs. VARCHAR</h4>
<ul><li><strong>CHAR(10)</strong> 始终占用 10 个字节（不足补空格）。</li><li><strong>VARCHAR(10)</strong> 只存储实际字符，最多 10 个字节，并额外占用 1-2 字节用于存储长度信息。</li></ul>
<p>一般来说：</p>
<ul><li><strong>短小、长度固定的文本</strong>（如国家代码、MD5 哈希）用 <code>CHAR</code>。</li><li><strong>长度不固定的文本</strong>（如姓名、地址）用 <code>VARCHAR</code>。</li></ul>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>1.2 TEXT 和 BLOB</h3>
<p>当存储较长的文本或二进制数据时，使用 <strong>TEXT 和 BLOB</strong> 类型：</p>
<table><thead><tr><th>数据类型</th><th>最大存储大小</th><th>是否区分大小写</th><th>适用场景</th></tr></thead><tbody><tr><td><strong>TINYTEXT</strong></td><td>255 字节</td><td>✅ 区分大小写</td><td>短文本，如推文内容</td></tr><tr><td><strong>TEXT</strong></td><td>65,535 字节（64KB）</td><td>✅ 区分大小写</td><td>文章内容、评论</td></tr><tr><td><strong>MEDIUMTEXT</strong></td><td>16,777,215 字节（16MB）</td><td>✅ 区分大小写</td><td>书籍、日志记录</td></tr><tr><td><strong>LONGTEXT</strong></td><td>4GB</td><td>✅ 区分大小写</td><td>超长文本，如百科条目</td></tr><tr><td><strong>TINYBLOB</strong></td><td>255 字节</td><td>❌ 不区分大小写</td><td>小二进制数据，如图片缩略图</td></tr><tr><td><strong>BLOB</strong></td><td>64KB</td><td>❌ 不区分大小写</td><td>图片、音频</td></tr><tr><td><strong>MEDIUMBLOB</strong></td><td>16MB</td><td>❌ 不区分大小写</td><td>大型媒体文件</td></tr><tr><td><strong>LONGBLOB</strong></td><td>4GB</td><td>❌ 不区分大小写</td><td>超大文件，如电影</td></tr></tbody></table>
<p class="maodian"><a name="_label3_1_1_1"></a></p><h4>TEXT vs. VARCHAR</h4>
<ul><li><code>TEXT</code> 不支持 <strong>默认值</strong>，也无法使用 <strong>索引（仅支持前缀索引）</strong>，但可以存储大量文本。</li><li><code>VARCHAR</code> 可以索引整个字段，适用于需要频繁搜索的文本字段。</li></ul>
<p class="maodian"><a name="_label2"></a></p><h2>2. 数字（Numeric）数据类型</h2>
<p>数字类型用于存储整数或小数，主要分为<strong>整数类型</strong>和<strong>浮点/定点类型</strong>。</p>
<p class="maodian"><a name="_lab2_2_2"></a></p><h3>2.1 整数类型</h3>
<table><thead><tr><th>数据类型</th><th>存储大小</th><th>取值范围（无符号）</th><th>适用场景</th></tr></thead><tbody><tr><td><strong>TINYINT</strong></td><td>1 字节</td><td>0 ~ 255</td><td>布尔值（0/1）、评分</td></tr><tr><td><strong>SMALLINT</strong></td><td>2 字节</td><td>0 ~ 65,535</td><td>小范围数值，如年龄</td></tr><tr><td><strong>MEDIUMINT</strong></td><td>3 字节</td><td>0 ~ 16,777,215</td><td>适用于 ID、自增字段</td></tr><tr><td><strong>INT（INTEGER）</strong></td><td>4 字节</td><td>0 ~ 4,294,967,295</td><td>适用于大部分场景，如用户 ID</td></tr><tr><td><strong>BIGINT</strong></td><td>8 字节</td><td>0 ~ 18,446,744,073,709,551,615</td><td>适用于超大数值，如银行账户余额</td></tr></tbody></table>
<p><strong>无符号（UNSIGNED） vs. 有符号</strong></p>
<ul><li><strong>默认情况下整数是有符号的</strong>（即支持负数）。</li><li><code>UNSIGNED</code> 关键字可以使整数仅存储<strong>非负数</strong>，从而扩大可用范围。<div class="jb51code"><pre class="brush:sql;">CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);
</pre></div></li></ul>
<p class="maodian"><a name="_lab2_2_3"></a></p><h3>2.2 浮点与定点类型</h3>
<table><thead><tr><th>数据类型</th><th>存储大小</th><th>适用场景</th></tr></thead><tbody><tr><td><strong>FLOAT(M, D)</strong></td><td>4 字节</td><td>适用于非严格精度的计算，如游戏评分</td></tr><tr><td><strong>DOUBLE(M, D)</strong></td><td>8 字节</td><td>高精度计算，如科学计算</td></tr><tr><td><strong>DECIMAL(M, D)（NUMERIC）</strong></td><td>可变</td><td>财务计算，避免精度丢失</td></tr></tbody></table>
<p class="maodian"><a name="_label3_2_3_2"></a></p><h4>FLOAT vs. DECIMAL</h4>
<ul><li><strong>FLOAT/DOUBLE 使用二进制存储，可能导致精度误差</strong>：
<div class="jb51code"><pre class="brush:sql;">SELECT 0.1 + 0.2; -- 结果可能不是 0.3
</pre></div></li><li><strong>DECIMAL 采用字符串存储，不会丢失精度</strong>：<div class="jb51code"><pre class="brush:sql;">CREATE TABLE transactions (
amount DECIMAL(10,2) NOT NULL
);
</pre></div></li></ul>
<p>建议：</p>
<ul><li><strong>财务类数据使用 DECIMAL</strong>，避免计算误差。</li><li><strong>非关键计算（如统计数据）可使用 FLOAT 或 DOUBLE</strong>。</li></ul>
<p class="maodian"><a name="_lab2_2_4"></a></p><h3>3. 日期与时间（Date & Time）</h3>
<p>MySQL 提供多个日期时间类型：</p>
<table><thead><tr><th>数据类型</th><th>存储大小</th><th>取值范围</th><th>适用场景</th></tr></thead><tbody><tr><td><strong>DATE</strong></td><td>3 字节</td><td>1000-01-01 ~ 9999-12-31</td><td>生日、订单日期</td></tr><tr><td><strong>DATETIME</strong></td><td>8 字节</td><td>1000-01-01 00:00:00 ~ 9999-12-31 23:59:59</td><td>需要精确到秒的时间，如创建时间</td></tr><tr><td><strong>TIMESTAMP</strong></td><td>4 字节</td><td>1970-01-01 00:00:01 ~ 2038-01-19 03:14:07</td><td>适用于存储 Unix 时间戳</td></tr><tr><td><strong>TIME</strong></td><td>3 字节</td><td>-838:59:59 ~ 838:59:59</td><td>记录时间间隔</td></tr><tr><td><strong>YEAR</strong></td><td>1 字节</td><td>1901 ~ 2155</td><td>适用于年份数据</td></tr></tbody></table>
<p class="maodian"><a name="_label3_2_4_3"></a></p><h4>DATETIME vs. TIMESTAMP</h4>
<ul><li><strong>TIMESTAMP 受时区影响</strong>，适合存储事件发生时间。</li><li><strong>DATETIME 不受时区影响</strong>，适合存储固定时间。</li></ul>
<div class="jb51code"><pre class="brush:sql;">CREATE TABLE events (
event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
</pre></div>
<p class="maodian"><a name="_lab2_2_5"></a></p><h3>4. 选择数据类型的最佳实践</h3>
<ol><li><strong>能用整数就不要用字符串</strong>（如性别可用 <code>TINYINT</code>）。</li><li><strong>存储金额、财务数据时，使用 DECIMAL 而非 FLOAT</strong>。</li><li><strong>短文本（<255 字符）用 VARCHAR，长文本用 TEXT</strong>。</li><li><strong>索引字段尽量避免 TEXT 和 BLOB，影响查询性能</strong>。</li><li><strong>时间戳数据尽量使用 TIMESTAMP 代替 DATETIME，节省存储空间</strong>。</li></ol>
<p class="maodian"><a name="_label3"></a></p><h2>总结</h2>
<p>MySQL 提供了多种数据类型，每种类型都有其适用场景。合理选择数据类型可以提升存储效率、优化查询性能，并避免精度损失。希望这篇文章能帮助你在数据库设计时做出更好的选择！</p>
頁:
[1]