Jump to content

Is there a limit to columns?


phppup

Recommended Posts

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.

 

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.
  :o
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.