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? 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. 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? 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. 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)? 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. 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? 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 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! 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. 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. 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. 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. 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
Archived
This topic is now archived and is closed to further replies.