Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re^2: Reversing a mysql table

by gogoglou (Beadle)
on Apr 07, 2010 at 11:53 UTC ( #833275=note: print w/replies, xml ) Need Help??

in reply to Re: Reversing a mysql table
in thread Reversing a mysql table

I tried that but it doesn't work. it is a huge table with ~2300 rows and 36 columns. I need to turn it into 2300 columns and 36 rows

Replies are listed 'Best First'.
Re^3: Reversing a mysql table
by CountZero (Bishop) on Apr 07, 2010 at 13:22 UTC
    In theory it is possible and Perl could probably through DBI and DBD::mysql slurp in the whole database, transpose it in memory and write it all back to a new table with rows and columns exchanged.

    However, there are certain limits in MySQL (from the v.5.0 docs):

    D.7.2. The Maximum Number of Columns Per Table

    There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors, listed in the following discussion.

    Every table has a maximum row size of 65,535 bytes. This maximum applies to all storage engines, but a given engine might have additional constraints that result in a lower effective maximum row size.

    The maximum row size constrains the number of columns because the total width of all columns cannot exceed this size. For example, utf8 characters require up to three bytes per character, 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.

    Will your transposed data fit within these limits?

    Another very practical question: what will be the column-names? Is there a way to generate them automatically?


    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Re^3: Reversing a mysql table
by tospo (Hermit) on Apr 08, 2010 at 22:35 UTC

    2300 rows is not huge - millions of rows is huge :-) The transformation should not be a problem in perl, e.g. by reading into an array of arrays with the DBI and then traverse it with reversed indices, printing out a series of "INSERT INTO table VALUES (),(),()..." statements into a sql file. Add a "drop table" statement and you can use this to repopulate your table from the file.

    However, a table with 36 rows and 2300 columns seems very wrong and it might be better to take the opportunity to redesign the database. If you can describe the database it would be easier to give more specific help.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://833275]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (3)
As of 2022-11-28 11:12 GMT
Find Nodes?
    Voting Booth?