可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
Presuming that the spaces are not important in a field's data, is it good practice to trim off the spaces when inserting, updating or selecting data from the table ?
I imagine different databases implement handling of spaces differently, so to avoid that headache, I'm thinking I should disallow leading and trailing spaces in any field data.
What do you think?
回答1:
I think it is a good practice. There are few things more soul crushing than spending an hour, a day, or any amount of time, chasing down a bug that ultimately was caused by a user typing an extra space. That extra space can cause reports to go subtly wrong, or can cause an exception somewhere in your program, and unless you have put brackets around every print statement in your logs and error messages, you might not realize that it is there. Even if you religiously trim spaces before using data you've pulled from the db, do future users of your data a favor and trim before putting it in.
回答2:
If leading and trailing spaces are unimportant, then I'd trim them off before inserting or updating. There should then be no unnecessary spaces on a select.
This brings some advantages. Less space required in a row means that potentially more rows can exist in a data page which leads to faster data retrieval (less to retrieve). Also, you are not constantly trimming data on SELECTs. (Uses the DRY [don't repeat yourself] principle here)
回答3:
I would say it's a good practice in most scenarios. If you can confidently say that data is worthless, and the cost of removing it is minimal, then remove it.
回答4:
I would trim them (unless you are actually using the whitespace data), simply because it is easy to do, and spaces are particularly hard to spot if they do cause problem in your code.
回答5:
For typical data enty it's not worth the overhead. Is there some reason you think you are going to get lots of extra blank lines? If you are then it might be a good idea to trim to keep DB size down but otherwise no.
回答6:
Trailing spaces are particularly problematic, specifically in regards ANSI_NULLS behaviour.
For instance, colname = '1' can return true where colname like '1' returns false
Thus, given trailing spaces in varchar columns are ambiguous, truncation is most likely preferable, particularly because there is no real information in such data and it creates ambiguity in the behaviour of SQL Server.
For example, look at the discussion at this question:
Why would SqlServer select statement select rows which match and rows which match and have trailing spaces
回答7:
Handling trailing spaces is a good practise. It is a common mistake in databases and it leads to long searching of mistakes.
Either trim them during insert/ update, or add a check clause to your table like this:
ALTER TABLE tblData
WITH CHECK ADD CONSTRAINT CK_Spaces_tblData
CHECK
(
datalength(USERID)>(0)
AND datalength(ltrim(rtrim(USERID)))=datalength(USERID)
)
In this case, users get an error when they try to insert or update.
This has the advantage, that users know about the mistake. Very often, they already have trailing spaces in some Excel sheet, and then they copy-paste. So it's good for them to know about this, so they can remove the error also in their excel sheets.