May 2025
Beginner to intermediate
974 pages
14h 39m
Chinese
Content preview from 《MySQL 烹饪书》第 4 版Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.
第 14 章 验证和重新格式化数据 验证和重新格式化数据
本作品已使用人工智能进行翻译。欢迎您提供反馈和意见：translation-feedback@oreilly.com
14.0 引言
上一章，即第 13 章，主要介绍了 方法，通过读取行并将其拆分成单独的列，将数据移入和移出 MySQL。在本章中，我们将重点讨论内容而非结构问题。 例如，如果不知道文件中包含的值或通过网络表单接收的值是否合法，可以对它们进行预处理，检查或重新格式化它们：
对数据值进行验证通常是个好主意，以确保这些值对于存储它们的数据类型是合法的。例如，可以确保
INT、DATE和ENUM列的值是整数、ISO 格式的日期 (YYYY-MM-DD) 和合法的枚举值。数据值可能需要重新格式化。您可以将信用卡值存储为数字字符串，但允许网络应用程序用户用空格或破折号分隔数字块。这些值必须在存储前重写。将日期从一种格式重写为另一种格式的情况尤其常见，例如，如果程序将日期从
MM-DD-YY格式写入 ISO 格式以导入 MySQL。如果程序只理解日期和时间格式，而不理解日期和时间的组合格式（如 MySQL 使用的DATETIME和TIMESTAMP数据类型），则必须将日期和时间值分割成单独的日期和时间值。
本章主要在检查整个文件的背景下讨论格式化和验证问题，但这里讨论的许多技术也可用于一次性验证。考虑一个基于 Web 的应用程序，该应用程序提供一个表单供用户填写，然后处理表单内容，在数据库中创建新行。网络 API 通常会将表单内容作为一组已经解析过的离散值提供，因此应用程序可能不需要处理记录和列分隔符。另一方面，验证问题仍然至关重要。你真的不知道用户会向你的脚本发送什么样的值，因此检查这些值非常重要。
前三个配方介绍了 MySQL 中可用的数据验证功能。从配方 14.4 开始，我们将重点关注在应用程序端验证和预处理数据。我们将介绍可以有效处理大量数据的技术。
本 章中讨论的程序片段和脚本的源代码位于recipes 发行版的transfer目录中，但某些实用程序函数包含在lib目录中的库文件中。
14.1 使用 SQL 模式剔除不良输入值
问题
MySQL 接受无效的数据值、超出范围的 数据值或其他不适合您插入数据的列的数据类型的数据值。您希望服务器有更多限制，不接受不良数据。
解决方案
检查 SQL 模式，确保它不是空的。有几种模式可以用来控制服务器对数据值的严格程度。有些模式一般适用于所有输入值，有些则适用于特定数据类型，如日期。
讨论
当 SQL 模式未设置或设置为空值时，MySQL 允许你的表列的所有输入值，即使输入的数据类型与列的数据类型不匹配。请看下表，其中有整数、字符串和日期列：
mysql> SELECT @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 ...Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access
More than 5,000 organizations count on O’Reilly
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.Julian F.
I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.Addison B.
I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.Amir M.
I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.