Is there a limit to columns?

[phppup]

Is there a limit to the number of columns a table can have?

 

100?  200??  some crazy large number not to be concerned with?

[kicken]

I don't believe there is a limit on the number of columns, persay.  The limit is on how big an individual row can be in bytes.  So if you had a bunch of TINYINT columns you could fit more in than if you had a bunch of VARCHAR(255) columns, for example.

 

Regarless, if your hitting these limits chances are your probably doing something wrong and need to re-think your DB design.

 

Perhaps someone more familiar with the inner workings of mysql could give a more detailed answer.

 

[phppup]

That's probably suffiecient.

Now I see why it might be advantages to using TINYINT's instead of VARCHARs.

 

Nonetheless, a row with 150 or 200 VARCHAR columns shouldn't be a problem, should it?

[DavidAM]

See http://dev.mysql.com/doc/refman/5.1/en/column-count-limit.html

 

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.  ... so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns.

[phppup]

So what happens if you go over? 

Does it just drop the last 115 columns  (200 intended -85 suggested max)?

Is there some sort of warning message triggered when the table is created (or surpassing the limits)?

[DavidAM]

I suspect that the CREATE TABLE statement will fail.

[phppup]

Read the link, thanks!

 

Is there a way to confirm a table or row SIZE so that this is known BEFORE adding additional columns or running into trouble?

[mikosiko]

Is there a way to confirm a table or row SIZE so that this is known BEFORE adding additional columns or running into trouble?

 

if you are adding columns dynamically on you table, as your question seems to imply, then definitively something is very wrong in your design

[phppup]

Why do you say that?

 

And if you READ the ABOVE comments, this is just basic information!

[DavidAM]

There is an explanation of the calculation in the manual under Row Size Limits

 

And here is an example of how helping others teaches me things:

 

Thus, using long column names can reduce the maximum number of columns, as can the inclusion of ENUM or SET columns, or use of column, index, or table comments.

 

[Pikachu2000]

 

if you are adding columns dynamically on you table, as your question seems to imply, then definitively something is very wrong in your design

 

Exactly.

[fenway]

If your table has more than 50, it's broken.

[The Little Guy]

If your table has more than 50, it's broken.

 

Where is the like button? I say that is still a high number though. Good tables have 3 or 2 columns IMO (Key, Value tables) then join them all.