phppup Posted March 1, 2012 Share Posted March 1, 2012 Is there a limit to the number of columns a table can have? 100? 200?? some crazy large number not to be concerned with? Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/ Share on other sites More sharing options...
kicken Posted March 1, 2012 Share Posted March 1, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/#findComment-1322868 Share on other sites More sharing options...
phppup Posted March 1, 2012 Author Share Posted March 1, 2012 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? Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/#findComment-1322872 Share on other sites More sharing options...
DavidAM Posted March 1, 2012 Share Posted March 1, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/#findComment-1322876 Share on other sites More sharing options...
phppup Posted March 1, 2012 Author Share Posted March 1, 2012 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)? Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/#findComment-1322909 Share on other sites More sharing options...
DavidAM Posted March 1, 2012 Share Posted March 1, 2012 I suspect that the CREATE TABLE statement will fail. Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/#findComment-1322911 Share on other sites More sharing options...
phppup Posted March 1, 2012 Author Share Posted March 1, 2012 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? Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/#findComment-1322922 Share on other sites More sharing options...
mikosiko Posted March 1, 2012 Share Posted March 1, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/#findComment-1322925 Share on other sites More sharing options...
phppup Posted March 1, 2012 Author Share Posted March 1, 2012 Why do you say that? And if you READ the ABOVE comments, this is just basic information! Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/#findComment-1322927 Share on other sites More sharing options...
DavidAM Posted March 2, 2012 Share Posted March 2, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/#findComment-1322982 Share on other sites More sharing options...
Pikachu2000 Posted March 2, 2012 Share Posted March 2, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/#findComment-1322986 Share on other sites More sharing options...
fenway Posted March 3, 2012 Share Posted March 3, 2012 If your table has more than 50, it's broken. Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/#findComment-1323497 Share on other sites More sharing options...
The Little Guy Posted March 6, 2012 Share Posted March 6, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/258070-is-there-a-limit-to-columns/#findComment-1324358 Share on other sites More sharing options...
.thumb.jpeg.5da54518df0b69c737e8dabbcace30bb.jpeg)
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.