Insert rows in middle of a table?

b01

Is there any way to insert rows at the beginning and middle of a table?

devinemke

why?

Drakla

If you were really desperate to do it you could mirror the structure of the table and start doing layers of new and old data - but again - why do you want it?

b01

I wanted to use a table to create and organize a menu system. Being able to insert rows at the begginning and middle of a table would help simplify the process.

Currently I have simulated this by creating a column within the table called Sequence.

Before a new row is inserted, an if statement runs to see if the position in the menu is taken, if so, then an update statement is run to make room for the new menu item.

For example:

Menu Item - Sequence

Home - -1
Links - 0
Login - 1

Using an HTML form, I can insert a new item named "Games", after "Home." The form is submitted and a PHP if statement is run
to check if the Sequence '0' is taken, if so then run an UPdate that will add 1 to anything >= 0. Then the new item is inserted.

Being able to Insert rows in the middle of a table would have cut out on this logic and saved time.

But I see since the community has no need for it, they never bothered to think or implement this. It seems VERY strange that MySQL cannot do INSERT into a table other than at the end of it. The MySQL community thinks TOO linear for me!

devinemke

i sugget you re-think all this. don't think of a database table as a bunch of sequential rows of data. it's not. it's just a bunch a data in no particular order. only when you retrieve the data using a SELECT do you order the data using in the ORDER BY clause in your query. until then, again it's just a big blob of data.

as far as your particular app is concerned: if you are trying to contruct a tree-like menu system, a common way of doing this is to have a field call parent_id (that defaults to NULL) that relates to the primary key field of the same table. for example:

table: menu
id (INT)
parent_id (INT)
name (VARCHAR)

example records might look like:

id: 1
parent_id: NULL
name: fruit

id: 2
parent_id: NULL
name: vegatable

id: 3
parent_id: 1
name: apple

id: 4
parent_id: 1
name: orange

id: 5
parent_id: 2
name: lettuce

id: 6
parent_id: 3
name: macintosh

id: 7
parent_id: 5
name: romaine

then when put all together:

-fruit
--apple
---macintosh
--orange
-vegatable
--lettuce
---romaine

make sense?

Sxooter

Yeah, devinmke has a point. Relational theory has no natural ordering, that's what "order by" is for.

For instance, in postgresql (and possibly other databases) you can have this:

test=# create table test (id int, info text);
CREATE TABLE
test=# insert into test values (1,'abc');
INSERT 6262902 1
test=# insert into test values (2,'def');
INSERT 6262903 1
test=# insert into test values (3,'ghi');
INSERT 6262904 1
test=# select * from test;
 id | info
----+------
  1 | abc
  2 | def
  3 | ghi
(3 rows)

test=# update test set info='xyz' where id=2;
UPDATE 1
test=# select * from test;
 id | info
----+------
  1 | abc
  3 | ghi
  2 | xyz
(3 rows)

Notice how the "native" ordering just changed. While you might like the idea of having an intrinsic ordering, the fact is that the SQL spec specifically says there isn't one. And thank goodness there isn't, imagine the overhead to certain databases if they had to maintain order on tables with billions or rows...

IF you want order, you must provide it. Note that a view can do this:

test=# create view tview as select * from test order by id;
CREATE VIEW
test=# select * from tview;
 id | info
----+------
  1 | abc
  2 | xyz
  3 | ghi
(3 rows)

Now, if you're going to be doing a LOT of middle inserts, then it's a good idea to insert your records with gaps in the ids. For most databases, you can set the step of a sequence used for this:

test=# create sequence test_seq increment by 20;
CREATE SEQUENCE
test=# create table test (id int primary key default nextval('test_seq'), info text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
test=# insert into test values (DEFAULT,'abc');
INSERT 6262920 1
test=# insert into test values (DEFAULT,'def');
INSERT 6262921 1
test=# insert into test values (DEFAULT,'ghi');
INSERT 6262922 1

NOTE that the rows now have 20 free spaces between them here:

test=# select * from test;
 id | info
----+------
  1 | abc
 21 | def
 41 | ghi
(3 rows)

Now we add a row in the middle:

test=# insert into test values (10,'abb');
INSERT 6262923 1

Note that without an order by, it's at the "bottom"

test=# select * from test;
 id | info
----+------
  1 | abc
 21 | def
 41 | ghi
 10 | abb
(4 rows)

We add our order by and there we have it:

test=# select * from test order by id;
 id | info
----+------
  1 | abc
 10 | abb
 21 | def
 41 | ghi
(4 rows)

All this was done with postgresql. Not sure how much of it will work or how to make it work with MySQL.

b01

devinemke, I like your idea alot, in fact it was my initial one, but what if I want to add an item before fruit, how would I do that?

Sxooter, that was one of my recent ideas, but that involves writing a function that extracts the data from the table, organizes it and inserts the new item where neccessary, then update whatever rows as needed. That is just too much overhead for something so simple.

The method I devised is similar, but much less overhead. it simply checks to see if the position is taken, if so, then it performs an update that makes room for the new item, then the item is inserted.

It's sort of a mix of both previous methods.

Both your ponits are fairly unreasonbel, because the point of a database is NOT only to store information, but to ORGANIZE that very data as well. The SELECT statement is merely a way of presenting that data in various ways, so as not to be static, and preventing users from having to create multiple tables with the same data but in different arragements.

Appearently the creators of MySQL and other databases have forgotten the fact that databases are not just for storing information, but also to help organize it as well.

Anyone can create a program that dumps a bunch of data in a text file with a 3 letter extension; and seeing as the programmers do not intend MySQL to help organize the data in an optimal way, I should think of making my own database program, even if only for personal use.

You guys don't see a need for this now, but if you get into constructing databases for different users, something like this just seems common sense.

I bet if they were to implement this in MySQL, you would get a rush of people in here with ideas and comments on how they would use it.

devinemke

Originally posted by b01
devinemke, I like your idea alot, in fact it was my initial one, but what if I want to add an item before fruit, how would I do that?

update it's parent_id

Drakla

indexes are there for ordering data and retrieving them in defined order as quickly as possible.

Remember that databases are just entries on a hard drive. When you add a file to your hard drive you don't expect it to totally defragment itself to put a filename in the middle of the drive.

So an order field with and index, that's what you need.

Sxooter

b01, you seem pretty new to this whole "database" thing. while my solution seems overly complex to you, you don't seem to realize how overly complex MySQL would be if it mainained some kind of "natural" ordering like you want.

Imagine that you create a table and insert 1,000,000 rows. Now, you want to insert a row at a point in the middle. MySQL has to move 500,000 rows down one spot to do it. That could take several minutes.

In the meantime, all the other users trying to access that table are held up waiting for your insert.

Whether the MySQL team implements this insane idea or you do, it is a performance nightmare waiting to happen.

MY method, which seemed over complex to you, is actually quite simple to implement, and I've implemented the exact same types of setups in an afternoon, and they can handle 1,000 users reading and 100 users inserting at the same time with good performance.

Your problem is you're thinking of a database as something one person uses at a time. That's quite possibly the least interesting scenario for database there is, and it's called batch processing. If you're using a database to do batch processing only, you've chosen a poor tool for the job.

The primary purpose of a database is to store your data in such a way as to make sure it's coherent from one point in time to another. Displaying the data is the easy part.

I'd strongly suggest you spend some time studying database / relational theory, it'll help you in the future.

devinmike's post points out that your entire methodology here is basically wrong, in trying to create a tree structure in a flat table format. You should try his method.

csn

Sxooter - do you recommend creating tables with or without OID's? Aren't OID's somewhat deprecated?

Sxooter

OIDs are now optional, but by default, tables are created with each row having an OID.

Generally, I recommend no oids, unless you have some reason for needing them. This is especially true for very large tables.

Note that if you have already created a table, you can remove the oids with alter table...

b01

Sxooter, I dont see how creating a table with holes is complex, besides I didn't say that it was. Not to mention that I had already thought of that idea myself.

The fact is that your idea does not work for my application.

Sorry if you felt insulted!

This menu has a limit of 50 items, organizing it in a table would be nice. I woul never apply this to a database that has more than 100 rows of data at any given time.

Besides, there are plenty of ways to insert a row in the middle of a table without having to update every row, just think about it!😕

laserlight

Both your ponits are fairly unreasonbel, because the point of a database is NOT only to store information, but to ORGANIZE that very data as well.

That's not true.
It is up to the database user to organise the data for display.

This provides flexibility, since different users will have different requirements as to the organisation of data.
Not only that, but no requirement for guessing what optimisation the user wants in display allows database engines more freedom in their own optimisation in storage and processing.

Besides, there are plenty of ways to insert a row in the middle of a table without having to update every row, just think about it!

One way is precisely what devinemke suggested.