New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

What does varchar (255) mean? #35

Open
scripting opened this Issue Oct 10, 2017 · 9 comments

Comments

Projects
None yet
5 participants
@scripting
Owner

scripting commented Oct 10, 2017

In SQL you declare a text value by telling the system how much space to reserve for it.

I understand why that was necessary 50 years ago, but I can't imagine today's SQL databases don't use some kind of heap.

When I say a value is varchar (255) does that mean in every case it takes up 255 chars in the database?

@jswright61

This comment has been minimized.

Show comment
Hide comment
@jswright61

jswright61 Oct 10, 2017

No.
Char(255) allocates 255 bytes for every row.
Varchar(255) uses and offset plus the data, and each row takes up only the space it needs

jswright61 commented Oct 10, 2017

No.
Char(255) allocates 255 bytes for every row.
Varchar(255) uses and offset plus the data, and each row takes up only the space it needs

@scripting

This comment has been minimized.

Show comment
Hide comment
@scripting

scripting Oct 10, 2017

Owner

That's what I thought it would be, is the 255 just to be backward compatible in some way? What does it mean, why is it required?

Owner

scripting commented Oct 10, 2017

That's what I thought it would be, is the 255 just to be backward compatible in some way? What does it mean, why is it required?

@jswright61

This comment has been minimized.

Show comment
Hide comment
@jswright61

jswright61 Oct 10, 2017

255 is arbitrary except that it represents what can be stored in a one byte offset (I think MySQL refers to this as length). The max value is actually like 65,535 bytes which is the max length for a row in a MySQL table (Total length of all columns). The defined length (255) also represents where input would throw an error if it is longer - so inserting "123456" into a varchar(5) column would result in a "Data too long" error.

jswright61 commented Oct 10, 2017

255 is arbitrary except that it represents what can be stored in a one byte offset (I think MySQL refers to this as length). The max value is actually like 65,535 bytes which is the max length for a row in a MySQL table (Total length of all columns). The defined length (255) also represents where input would throw an error if it is longer - so inserting "123456" into a varchar(5) column would result in a "Data too long" error.

@galori

This comment has been minimized.

Show comment
Hide comment
@galori

galori Oct 10, 2017

So the “var” stands for variable. Exactly because it takes up a variable amount of space based on the string length.

galori commented Oct 10, 2017

So the “var” stands for variable. Exactly because it takes up a variable amount of space based on the string length.

@jswright61

This comment has been minimized.

Show comment
Hide comment
@jswright61

jswright61 Oct 10, 2017

you got it!

jswright61 commented Oct 10, 2017

you got it!

@scripting

This comment has been minimized.

Show comment
Hide comment
@scripting

scripting Oct 10, 2017

Owner

Not really sure I understand, but I'm going to keep using varchar (255) for things like a twitter screenname, knowing that "bullmancuso" won't take up 255.

Another question -- does MySQL keep any metadata for a row or do I have to do my own. For example, does it keep track of creation and mod dates for rows?

Owner

scripting commented Oct 10, 2017

Not really sure I understand, but I'm going to keep using varchar (255) for things like a twitter screenname, knowing that "bullmancuso" won't take up 255.

Another question -- does MySQL keep any metadata for a row or do I have to do my own. For example, does it keep track of creation and mod dates for rows?

@papascott

This comment has been minimized.

Show comment
Hide comment
@papascott

papascott Oct 10, 2017

I was going to say that sometimes you want to manage the allowed length of a VARCHAR since you only have 65535 bytes for a row, but I see another guy named Scott beat me to it. (hi @jswright61 :-) ) But BLOB and TEXT data types don't count against that limit.
As for create date and mod date, I think you have to keep track of that yourself, or at least create your own fields for them.

papascott commented Oct 10, 2017

I was going to say that sometimes you want to manage the allowed length of a VARCHAR since you only have 65535 bytes for a row, but I see another guy named Scott beat me to it. (hi @jswright61 :-) ) But BLOB and TEXT data types don't count against that limit.
As for create date and mod date, I think you have to keep track of that yourself, or at least create your own fields for them.

@danmactough

This comment has been minimized.

Show comment
Hide comment
@danmactough

danmactough Oct 10, 2017

Another question -- does MySQL keep any metadata for a row or do I have to do my own. For example, does it keep track of creation and mod dates for rows?

@scripting You can configure your table to do that for you. When you create your table (or you can alter your table) you define columns like:

-- This is altering an existing table named `my_table`
ALTER TABLE my_table
    ADD COLUMN created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Caution: I think that updated_at column definition requires MySQL >= v5.7.

But what those columns do is when you insert a row, created_at and updated_at are set to the current timestamp (as long as you don't provide alternative values), and when you update a row, updated_at (again, as long as you don't provide an alternative value) is set to the current timestamp.

danmactough commented Oct 10, 2017

Another question -- does MySQL keep any metadata for a row or do I have to do my own. For example, does it keep track of creation and mod dates for rows?

@scripting You can configure your table to do that for you. When you create your table (or you can alter your table) you define columns like:

-- This is altering an existing table named `my_table`
ALTER TABLE my_table
    ADD COLUMN created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Caution: I think that updated_at column definition requires MySQL >= v5.7.

But what those columns do is when you insert a row, created_at and updated_at are set to the current timestamp (as long as you don't provide alternative values), and when you update a row, updated_at (again, as long as you don't provide an alternative value) is set to the current timestamp.

@scripting

This comment has been minimized.

Show comment
Hide comment
@scripting

scripting Oct 11, 2017

Owner

@danmactough -- Thanks! I haven't yet got to updating already-entered values, but I'm finding it's all sensible, and I did a semester of this stuff a long time ago, but it's like riding a bicycle, it just comes back. I'm having a lot of fun with it.

Owner

scripting commented Oct 11, 2017

@danmactough -- Thanks! I haven't yet got to updating already-entered values, but I'm finding it's all sensible, and I did a semester of this stuff a long time ago, but it's like riding a bicycle, it just comes back. I'm having a lot of fun with it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment