f



VARCHAR ( n )

  When a field usually has about 8 characters, but there is
  no absolute upper limit (think of the first name of a person),
  is there any drawback in specifying a VARCHAR argument that
  is much larger, say, for example, �VARCHAR ( 9999 )�?

  We can safely assume that no first name will have more than
  100 characters, but is there any drawback to use an upper
  limit that is much larger? (in MySQL 5.7)

0
ram
11/22/2016 12:38:55 PM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

13 Replies
284 Views

Similar Articles

[PageSpeed] 35

On 22.11.2016 13:38, Stefan Ram wrote:
>   We can safely assume that no first name will have more than
>   100 characters, but is there any drawback to use an upper
>   limit that is much larger? (in MySQL 5.7)

VARCHAR() fields are stored as the actual content and the length. Hence you
earn a penalty when you specify a VARCHAR() length > 255 because then 2
bytes are needed to store the actual length. Same goes for the different
BLOB and TEXT types which use 1, 2, 3 or 4 bytes for the length.

The manual here:

https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

suggests that even a VARCHAR(1000) field uses an 8-bit length specifier as
long as the *content* of the field is shorter than 256 bytes. But this might
be wrongly documented. I also remember that different storage engines can
handle things differently internally. I.e. InnoDB stores CHAR() and
VARCHAR() in the same format internally and pads CHAR() fields only when
they are read. Also InnoDB uses some extra logic to store long VARCHAR()
fields outside the row. By specifying a shorter maximum length, some of that
logic can be bypassed which saves some CPU cycles.

In the end I think the drawbacks from specifying a limit higher than
necessary are negligible. But then OTOH it is good practice to always use
the smallest possible data type and I see no point why to deviate from that.


XL
0
Axel
11/22/2016 1:17:08 PM
Axel Schwenke <axel.schwenke@gmx.de> writes:
>                                        it is good practice to always use
>the smallest possible data type

  The problem is that there often is no precise
  upper limit for the length of some kind of data (texts).

  For example, a first name usually is no longer
  than 32 characters, but when one uses VARCHAR ( 32 ),
  it might happen that eventually someone with 
  33 characters in his first name comes along. So,
  one might use VARCHAR ( 33 ), but then someone
  with 34 characters might appear.

  (This might be related to the

en.wikipedia.org/wiki/Sorites_paradox

  .) See also,

Keihanaikukauakahihuliheekahaunaele

0
ram
11/22/2016 1:27:04 PM
On 22/11/16 13:27, Stefan Ram wrote:
> Axel Schwenke <axel.schwenke@gmx.de> writes:

>   .) See also,
>
> Keihanaikukauakahihuliheekahaunaele
>
Must I?

-- 
"Corbyn talks about equality, justice, opportunity, health care, peace, 
community, compassion, investment, security, housing...."
"What kind of person is not interested in those things?"

"Jeremy Corbyn?"

0
The
11/22/2016 2:18:33 PM
On 22/11/16 13:27, Stefan Ram wrote:
> Axel Schwenke <axel.schwenke@gmx.de> writes:
>>                                        it is good practice to always use
>> the smallest possible data type
>
>   The problem is that there often is no precise
>   upper limit for the length of some kind of data (texts).
>
>   For example, a first name usually is no longer
>   than 32 characters, but when one uses VARCHAR ( 32 ),
>   it might happen that eventually someone with
>   33 characters in his first name comes along. So,
>   one might use VARCHAR ( 33 ), but then someone
>   with 34 characters might appear.
>
>   (This might be related to the
>
> en.wikipedia.org/wiki/Sorites_paradox
>
>   .)


Most philosophical 'issues' disappear under the impact of 'plain common 
sense' which understands that language is(nearly) always approximate.

If I pluck a whisker from my pet cat, is the whisker still part of e 
cat? Is the cat no longer a cat because it's lost a whisker?

Hair splitting.

Only  relevant to those who think that a 'cat' really exists in some 
precise way, rather than as a loose label for an associated bunch of 
aural, visual, tactile and olfactory sensations that seem to occur 
concurrently.

Not really much to do with data definitions, except insofar as to 
highlight the fact that a 'word' or a 'name' is an equally loose term.

-- 
"Corbyn talks about equality, justice, opportunity, health care, peace, 
community, compassion, investment, security, housing...."
"What kind of person is not interested in those things?"

"Jeremy Corbyn?"

0
The
11/22/2016 2:24:33 PM
On 11/22/2016 8:27 AM, Stefan Ram wrote:
> Axel Schwenke <axel.schwenke@gmx.de> writes:
>>                                        it is good practice to always use
>> the smallest possible data type
> 
>   The problem is that there often is no precise
>   upper limit for the length of some kind of data (texts).
> 
>   For example, a first name usually is no longer
>   than 32 characters, but when one uses VARCHAR ( 32 ),
>   it might happen that eventually someone with 
>   33 characters in his first name comes along. So,
>   one might use VARCHAR ( 33 ), but then someone
>   with 34 characters might appear.
> 
>   (This might be related to the
> 
> en.wikipedia.org/wiki/Sorites_paradox
> 
>   .) See also,
> 
> Keihanaikukauakahihuliheekahaunaele
> 

Sure there is an upper limit - it's whatever you specify.  If someone
has a 33 character first name, he/she is used to this.  Don't try to
cover every possibility - it's impossible because you have limits.  The
most common limit I've seen for first and last names is around 16
characters.  I don't think I've ever seen anything greater than 32
characters - although I'm sure it exists.


-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================
0
Jerry
11/22/2016 3:29:19 PM
Stefan Ram wrote:

> Axel Schwenke <axel.schwenke@gmx.de> writes:
>>                                        it is good practice to always use
>>the smallest possible data type
> 
>   The problem is that there often is no precise
>   upper limit for the length of some kind of data (texts).

“Texts” as in the TEXT data type?
 
>   For example, a first name usually is no longer
>   than 32 characters, but when one uses VARCHAR ( 32 ),
>   it might happen that eventually someone with
>   33 characters in his first name comes along. So,
>   one might use VARCHAR ( 33 ), but then someone
>   with 34 characters might appear.

<https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/>
 
-- 
PointedEars

Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.
0
Thomas
11/22/2016 8:10:07 PM
On 22.11.2016 14:27, Stefan Ram wrote:
> Axel Schwenke <axel.schwenke@gmx.de> writes:
>> it is good practice to always use
>> the smallest possible data type
>=20
>   The problem is that there often is no precise
>   upper limit for the length of some kind of data (texts).

So what?

You always have practical limits. The number of character in a book is
limited, because size and number of pages has an upper limit and the
character size has a lower limit.

Of course you could print in microdots, use pages of 10m=B2 and stack 10
million of those pages. But nobody would call that a book any more.

>   For example, a first name usually is no longer
>   than 32 characters, but when one uses VARCHAR ( 32 ),
>   it might happen that eventually someone with=20
>   33 characters in his first name comes along. So,
>   one might use VARCHAR ( 33 ), but then someone
>   with 34 characters might appear.

Again. So what?

There are also practical limits for first names. You cannot print an
arbitrary number of characters in a passport, on a business card, a fligh=
t
ticket or any other place where first names usually go.

If you are not sure what a good limit is, imagine (or research) what a
practical upper limit could be and then double it. Done.

0
Axel
11/23/2016 8:32:57 AM
On 22.11.2016 21:10, Thomas 'PointedEars' Lahn wrote:
> 
> <https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/>

OMFG! What a pile of bullshit. How is that supposed to help anybody in any
way solving a real-world problem?

0
Axel
11/23/2016 8:34:19 AM
Axel Schwenke wrote:

> On 22.11.2016 21:10, Thomas 'PointedEars' Lahn wrote:
>> <https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/>
> 
> OMFG! What a pile of bullshit. How is that supposed to help anybody in any
> way solving a real-world problem?

Well, it would obviously not help presumptuous know-it-alls.  However, more 
reasonable people, who would read more carefully, might take it as a word of 
caution, which is why I posted it as an *aside*.

-- 
PointedEars

Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.
0
Thomas
11/23/2016 1:09:00 PM
On 11/23/2016 8:09 AM, the well-known troll Thomas 'Pointed Head' Lahn
wrote:
> Axel Schwenke wrote:
> 
>> On 22.11.2016 21:10, Thomas 'PointedEars' Lahn wrote:
>>> <https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/>
>>
>> OMFG! What a pile of bullshit. How is that supposed to help anybody in any
>> way solving a real-world problem?
> 
> Well, it would obviously not help presumptuous know-it-alls.  However, more 
> reasonable people, who would read more carefully, might take it as a word of 
> caution, which is why I posted it as an *aside*.
> 

It didn't help you then, did it?

This is one case where I agree with Axel.  But it's also just they type
of bullshit you espouse.  It is nothing more than the opinions of one
person with a blog.  And opinions not shared by knowledgeable people.
But just right for your level.  I can see why you think it's so great.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================
0
Jerry
11/23/2016 7:59:21 PM
On 23/11/16 19:59, Jerry Stuckle wrote:
> On 11/23/2016 8:09 AM, the well-known troll Thomas 'Pointed Head' Lahn
> wrote:
>> Axel Schwenke wrote:
>>
>>> On 22.11.2016 21:10, Thomas 'PointedEars' Lahn wrote:
>>>> <https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/>
>>>
>>> OMFG! What a pile of bullshit. How is that supposed to help anybody in any
>>> way solving a real-world problem?
>>
>> Well, it would obviously not help presumptuous know-it-alls.  However, more
>> reasonable people, who would read more carefully, might take it as a word of
>> caution, which is why I posted it as an *aside*.
>>
>
> It didn't help you then, did it?
>
> This is one case where I agree with Axel.  But it's also just they type
> of bullshit you espouse.  It is nothing more than the opinions of one
> person with a blog.  And opinions not shared by knowledgeable people.
> But just right for your level.  I can see why you think it's so great.
>
I see u are still a sick sad complete cunt, jerry
why didn't I transfer my killfile to this machine...

-- 
Those who want slavery should have the grace to name it by its proper 
name. They must face the full meaning of that which they are advocating 
or condoning; the full, exact, specific meaning of collectivism, of its 
logical implications, of the principles upon which it is based, and of 
the ultimate consequences to which these principles will lead. They must 
face it, then decide whether this is what they want or not.

Ayn Rand.
0
The
11/23/2016 9:51:15 PM
On 11/23/2016 4:51 PM, The Natural Philosopher wrote:
> On 23/11/16 19:59, Jerry Stuckle wrote:
>> On 11/23/2016 8:09 AM, the well-known troll Thomas 'Pointed Head' Lahn
>> wrote:
>>> Axel Schwenke wrote:
>>>
>>>> On 22.11.2016 21:10, Thomas 'PointedEars' Lahn wrote:
>>>>> <https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/>
>>>>>
>>>>
>>>> OMFG! What a pile of bullshit. How is that supposed to help anybody
>>>> in any
>>>> way solving a real-world problem?
>>>
>>> Well, it would obviously not help presumptuous know-it-alls. 
>>> However, more
>>> reasonable people, who would read more carefully, might take it as a
>>> word of
>>> caution, which is why I posted it as an *aside*.
>>>
>>
>> It didn't help you then, did it?
>>
>> This is one case where I agree with Axel.  But it's also just they type
>> of bullshit you espouse.  It is nothing more than the opinions of one
>> person with a blog.  And opinions not shared by knowledgeable people.
>> But just right for your level.  I can see why you think it's so great.
>>
> I see u are still a sick sad complete cunt, jerry
> why didn't I transfer my killfile to this machine...
> 

I see you are still a trolling idiot.  The entire internet would be
better without you.

And you probably think his link is the slickest thing since snot on a
doorknob.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================
0
Jerry
11/24/2016 3:39:38 AM
On 22 Nov 2016 12:38:55 GMT, Stefan Ram wrote:
>   When a field usually has about 8 characters, but there is
>   no absolute upper limit (think of the first name of a person),
>   is there any drawback in specifying a VARCHAR argument that
>   is much larger, say, for example, »VARCHAR ( 9999 )«?
>
>   We can safely assume that no first name will have more than
>   100 characters, but is there any drawback to use an upper
>   limit that is much larger? (in MySQL 5.7)

No real problem. Make it as big as you think you'll need.

Frankly, you're probably going to run into bigger problems dealing with
the whole concept of "first name" "last name" than actual length issues.

What happens when Cher signs up? What happens when Nakamura Hiro signs
up? What happens if Nakamura Hiro wants to be 中村比呂 instead?

-- 
"Only Irish coffee provides in a single glass all four essential food
 groups: alcohol, caffeine, sugar, and fat."
			    -Alex Levine
0
Peter
11/27/2016 6:32:35 PM
Reply: