I have a MySQL table which has columns 93 in it. All first 92 columns are INT(11) and the last column is of type DATE.

|      | | | | ........|  |  |  |     |
|      | | | | ........|  |  |  |     |
|      | | | | ........|  |  |  |     |

A amount of 900000 entries per day is inserted to the table to the 2nd column("0" column) and at the end of the day the data in the columns must be shifted by one column to the right. So that at the next day. the insertion can be done to the "0" row again. Insertion to the rows is done by a c program. And the shifting is done by script. What should be the best practice to alter the table using the script to shift columns. I mean I tried alter table In a while loop which took a large time to finish.Any ideas

  • Couldn't you create a new view over the table each day and have the c program update that? It would push into the table and all you need to script is alter view xxx as ... with the definition changing as appropriate. If this is not a solution you can use, could you show the script you are currently using so we might see more specifically your issue? – Thronk Nov 5 '15 at 4:31
  • You should not have 90 columns shifting around. You should instead have 90 rows, inserting new one for new day (and deleting the old one if you need to). Alter table is only for a table definitions, not for data manipulations. – jkavalik Nov 5 '15 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.