user81993 user81993 - 1 month ago 7
MySQL Question

Is it possible to partially get/modify a field?

I'm setting up to gather long time statistics. It will be recorded in little blocks that I'm planning to stick all into one TEXT field, latest first.. sorta like this

[date:03.01.2016,data][date:02.01.2016,data][date:01.01.2016,data]...


it will be more frequent than that (just a sample) but should remain small enough to keep recording for decades, yet big enough to make me want to optimize it.

I'm looking for 2 things


  1. Can you append to the front of a field in mysql?

  2. Can you read the field partially, just the first 100 characters for example?



The blocks will be fixed length so I can accurately estimate how many characters I need to download to display statistics for X time period.

Answer

The answer to your two questions is "yes":

update t
    set field = concat($newval, field)
    where id = $id;

And:

select left(field, 100)
from t
where id = $id;

(These assume that you have multiple rows in the table.)

That said, you method of storing the data is absolutely not the right thing to do in a relational database.

Presumably, you want a table that looks something like this:

create table t (
    tId int auto_increment primary key,
    creationDate date,
    data <something>
);

(This may be more complicated if data should be multiple columns.)

Then you insert into the table:

insert into t(createDate, data)
    select $date, $data;

And you can fetch the most recent row:

select t.*
from t
order by tId desc
limit 1;

All of these are just examples, because your question doesn't give a complete picture of the data.

Comments