0

I have a table where each row contains a temperature and the weather condition such as:

temp  condition
17    cloudy
25    sunny
22    cloudy
41    sunny
12    showers
38    sunny

I am restricted for storage space so I wanted to cut down the size of this table. How can I merge the same weather conditions into one row where the temperature column has the different temperatures with a seperator such as "|"?

Once merged the table would look like:

temp       condition
17|22      cloudy
25|41|38   sunny
12         showers

I have changed the data type of the temp column to varchar to allow it to store the new format, but not sure how I can merge the rows now.

I want to overwrite this table with the new format as opposed to just combining it on select query. This will help to lower the file size and make it faster to run queries. If this cannot be done directly on mysql (phpmyadmin) then I would prefer to do this via a PHP script.

M9A
  • 3,168
  • 14
  • 51
  • 79
  • 2
    Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|193.3280) and just don't. – sticky bit May 27 '20 at 22:24
  • In addition to @stickybit's advice, I have a hard time believing that "these are the temperatures in places in which it is cloudy" is information that will be useful to _anyone_ no matter how much additional context you might try to attach. Don't do this. You will save virtually nothing, ruin your schema, and have to undo this down the line. – Sammitch May 27 '20 at 22:31
  • I don't recommend that at all. You can save a small amount of storage space by changing the condition to an enum (assuming it's varchar currently). Also ensure temp is a tinyint unless recording the apocalypse is a technical requirement. – rjdown May 27 '20 at 22:32
  • I find it hard to believe that storage space would be such an issue that this would be beneficial. Can you better explain? – Gordon Linoff May 27 '20 at 23:45
  • 'I am restricted for storage space' *this* cannot be true, and, if true, *that* cannot be the best strategy for addressing *this* – Strawberry May 28 '20 at 06:36
  • Your present strategy *definitely will not* make it faster to run queries – Strawberry May 28 '20 at 06:38

1 Answers1

0

You can use group_concat() to generate the results:

select
    group_concat(temp delimiter '|') temp,
    condition
from mytable
group by condition

As for overwriting the table, you would need to proceed in several steps:

  • create a new table to store the results of the query

  • feed it with the above query

  • drop the original table

  • rename the new table to the original name

I would not actually recommend going this direction. The tiny space gain that you will get will be overwhelmed by the drawbacks that storing data as delimited lists implies. I would recommend the following reading: Is storing a delimited list in a database column really that bad?, and a good second thought.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • I want to actually change the table to the new format rather than just view it like this through a select query – M9A May 27 '20 at 22:23