VARCHAR(x)和VARCHAR(y)在SQL中的差异

I have learnt that VARCHAR occupies the only memory which is required unlike CHAR which always occupy same amount of memory whether needed or not.

我了解到VARCHAR占用的内存是唯一需要的内存，不像CHAR，无论是否需要，它都会占用相同的内存。

My question: Suppose I have a field VARCHAR(50), I know that if it needs to store 30 characters, it will only occupy 30 bytes and no more than that(assuming 1 char takes one byte). So why should I even mention 50 or 30 or any upper limit, since it will only take the memory which is required.

我的问题是:假设我有一个字段VARCHAR(50)，我知道如果它需要存储30个字符，那么它只会占用30个字节，并且不会超过这个值(假设一个字符占用一个字节)。所以为什么我还要提到50或30或任何上限，因为它只会占用需要的内存。

UPDATE: Why do I have to mention the upper limit since there will be no useless memory occupied?

更新:为什么我必须提到上限，因为没有无用内存占用?

5 个解决方案

#1

UPDATE: Why do I have to mention the upper limit since there will be no useless memory occupied?

更新:为什么我必须提到上限，因为没有无用内存占用?

If you are sanitizing your inputs with something like final_value = left(provided_value, 30) then it's a non-issue for your database. You can set it to varchar(255) if you like.

如果您正在用final_value = left(provided_value, 30)之类的东西清理输入，那么这对您的数据库来说不是问题。如果愿意，可以将其设置为varchar(255)。

The idea of putting the max limit is to ensure you don't mistakenly send more chars than what you actually plan for.

设置最大限制的想法是确保你不会错误地发送比你实际计划的更多的chars。

Would be a pain in the future for code maintenance to recall the data size limit of every column of every table. You need to do that anyway but by looking at your table definitions as the single source for info about that.

对于代码维护来说，回想一下每个表的每个列的数据大小限制将是一种痛苦。无论如何，您都需要这样做，但是通过将表定义作为相关信息的单一来源来查看。
Would a table be written to (insert/update) from only one piece of code in your app or website? If there's another interface to the database like, say, a REST API listener, if you don't enter the same values again, you'll have an issue with non-uniform data - exactly what db's are able to prevent.

在你的应用程序或网站中，只有一段代码会被写入(插入/更新)表格吗?如果数据库中有另一个接口，比如REST API侦听器，如果您不再次输入相同的值，那么您就会遇到一个非统一数据的问题——确切地说，db能够阻止什么。
If a coding error (or hack) bypasses your app/website controls for data (size limits, or worse) then at least your db will still be maintaining the data correctly.

如果一个编码错误(或hack)绕过了你的应用/网站控件来获取数据(大小限制，或者更糟)，那么至少你的数据库仍然能够正确地维护数据。

#2

You wouldn't. You would make it VARCHAR(30). It's about the maximum amount of characters allowed. So why would you even make a column that takes 30 characters accept anything up to 50?

你不会。你可以把它做成VARCHAR(30)。它大约是允许的最大字符数。那么，为什么你还要写一个包含30个字符的专栏，接受最多50个字符的内容呢?

#3

to make things dynamic you are using VARCHAR(50) because in future the string size can be exceed and you knows that the maximum size can be 50, But for constant(s) you can use CHAR(30),this means that the size of string will be always 30 , my sql will report exception if the size exceed or decrease

让事情动态使用VARCHAR(50),因为在未来的字符串的大小可以超过你知道最大大小可以50,但对于常数(s)可以使用CHAR(30),这意味着字符串的大小将总是30,我的sql将报告异常如果大小超出或减少

take a look http://dev.mysql.com/doc/refman/5.0/en/char.html

看看http://dev.mysql.com/doc/refman/5.0/en/char.html

#4

When memory usage is your only concern, you can give any large number to varchar. But if you want to make sure that an upper limit is kept, than you give that as a maximum to varchar.

当内存使用是您唯一关心的问题时，您可以向varchar提供任何大的数字。但是如果你想要确定一个上限是保持不变的，而不是给varchar一个最大值。

#5

You can take VARCHAR(50) or VARCHAR(30). It's not a problem, but if it's dynamic we can't tell the limit.

你可以选VARCHAR(50)或VARCHAR(30)。这不是问题，但如果它是动态的，我们就不能告诉它极限。

In that case we take maximum limit.

在这种情况下，我们取最大值。

#1