即使是聪明人也可能不小心将不良数据放入数据库。
我们从网页复制文本，然后将其粘贴到表单中，却没有意识到它还复制了空格、制表符或换行符。然后你的系统认为某人的名字不是“Jim”而是“Jim\n”。
您可以使用 JavaScript 清理所有表单输入，但是当您导入 CSV 文件或从 API 获取数据时呢？
不，数据清理功能的最佳位置是数据库本身。因此，无论插入或更新什么代码，数据库触发器都会在保存之前对其进行清理。
这是一个 PostgreSQL 示例。让我们制作两张表，人员和电子邮件，这样您就可以看到许多触发器如何使用一个函数。
create table people ( id serial primary key, name text, code text ); create table emails ( id serial primary key, person_id integer not null references people(id), email text );
让我们做两个简单的函数：
- 删除所有空格，然后小写。
- 删除不需要的空白字符，如制表符和换行符。将它们全部替换为一个空格。然后从前端和末端修剪空间。
-- remove all whitespace, then lowercase it create function lower_no_space(text) returns text as $$ select lower(regexp_replace($1, '\s', '', 'g')); $$ language sql; -- replace all whitespace with single space, then trim start and end create function no_extra_space(text) returns text as $$ select btrim(regexp_replace($1, '\s+', ' ', 'g')); $$ language sql;
删除所有空格然后小写的功能适用于电子邮件地址和校验和等代码。
删除多余空格然后修剪的功能适用于许多事情，例如姓名、地址、电子邮件主题以及需要保留内部空格的任何地方。所以“新西兰\t新西兰\n”将是“新西兰”。
现在制作使用较小的可重复使用清洁功能的触发功能。我发现最好为每个表创建一个触发器，在任何插入或更新时清理所有字段。
create function clean_people() returns trigger as $$ begin new.name = no_extra_space(new.name); new.code = lower_no_space(new.code); return new; end; $$ language plpgsql; create trigger clean_people before insert or update on people for each row execute procedure clean_people(); create function clean_emails() returns trigger as $$ begin new.email = lower_no_space(new.email); return new; end; $$ language plpgsql; create trigger clean_emails before insert or update on emails for each row execute procedure clean_emails();
语言有点冗长，但是很好。样板代码的丑陋被数据库中处理所有这些的美丽简单所弥补。不必清理表单字段真是太好了！只需将未清洗的输入扔到数据库中即可。
在这里，让我们给它一些脏数据，然后看它干净。
insert into people (name, code) values (e' \t \r \n Dr. \n \r JM \t Lim \r\n', ' XX o Z ') returning *; -- id │ name │ code --────┼────────────┼────── -- 1 │ Dr. JM Lim │ xxoz insert into emails (person_id, email) values (1, e' \r\n \t DR. L @ JM Lim . com \n') returning *; -- id │ person_id │ email --────┼───────────┼──────────────── -- 1 │ 1 │ [email protected]
这种方法使我的代码更简单，数据更清晰。
在/code/clean1.sql下载完整的代码示例。
还要考虑使用触发器来确保数据完整性。