user81993 user81993 - 3 years ago 125
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


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 Source

The answer to your two questions is "yes":

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


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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download