Hi! I use mysql on amateurish level mainly for personal needs and so please forgive me if this feature request is impossible to fulfil or if it is sent to the wrong mail-list, or if this functionality has been already realized in other ways :)
Note: I've read the discription of C-function mysql_insert_id() and the
discription of SELECT LAST_INSERT_ID() in new versions, but as far as
I've understood it concerns only AUTOINCREMENT columns, and very often it
isn't enough (some columns may be filled by mysql functions (for example
data/time/mathematical functions etc) and very often the resulting
values are needed at once for report representation or for using in
the next statements INSERT)
So: The INSERT(UPDATE) statement returns the quantity of
strings inserted(updated) into a table.
(*) However as far as I understand at the stage of fulfilling this
operator mysql operates with these very strings.
Is it possible to add to the syntax of the INSERT operator appoximately
in such way:
INSERT [IGNORE] INTO ... - a general syntax
SELECT list INSERT [IGNORE] INTO ... - an added one.
UPDATE ... - a general syntax
SELECT list UPDATE .... - an added one.
Where can it be needed?
Example 1
~~~~~~~~~
We have a data base:
table (id, name, value1, value2, value3)
Now let's imagine CGI-script which makes an insert(update) and shows
the results to a user.
In the current case we need to:
1. INSERT INTO table (name, value1, value2, value3) VALUES
(?,?,?,?), (?,?,?,?),(?,?,?,?);
2. SELECT * FROM table WHERE ...
while the server has all the data needed for the step 2 already on the
step 1 :)
When selecting a few inserted strings at once we have either a complex
expression in the statement WHERE, or we need to split
the INSERT call into few single ones and replace 1 and 2 by the sequence
INSERT - SELECT - INSERT - SELECT.
It would be excellent to write:
SELECT * INSERT table (name, value1, value2, value3)
VALUES (?,?,?,?), (?,?,?,?), (?,?,?,?);
and, having on the entry the data for the insert on the exit, to get the
result of insert at once (and to display it if necessary)
similar UPDATE statement:
SELECT column1, column2
UPDATE table SET column3=value, column4=value
WHERE ...;
Example 2:
~~~~~~~~~~
For example we have a data base:
table1 (id, name) -- id - AUTOINCREMENT
table2 (id, table1_id, value1)
table3 (id, table1_id, value2)
Now let's imagine CGI-script working with such data base.
In case if it makes a data insert into this data base we need to:
1. INSERT [IGNORE] INTO table1 SET name=?
2. SELECT id FROM table1 WHERE name=?
or SELECT LAST_INSERT_ID() as id;
3. INSERT INTO table2 SET value1=?, table1_id=? -- value, id_fromt1
INSERT INTO table3 SET value2=?, table1_id=? -- value, id_fromt1
In the current situation all the data necessary for fulfillment of the
point 3 are actually available on mysql server when fulfilling point 1
however unfortunately it is impossible to extract them by making an
additional request :(
But if we had an additional syntax then we could unite points 1 and 2:
SELECT id INSERT INTO table1 SET name=?
And in some cases even points 1,2,3 altogether:
We insert in all the tables at once:
INSERT INTO table3 (table1_id, value2)
SELECT table1_id, ? INSERT INTO table2 (table1_id, value1)
SELECT id, ? INSERT INTO table1 SET name=?;
-- value2, value1, name
That is by adding the mirror statement SELECT...INSERT to the existing
statement INSERT...SELECT
we would gain a very interesting functionality, allowing sometimes to
get rid of using transactions and (or) to refuse from storage procedures
and to replace the mass colls by the single ones etc.
PS: I understand that adding the changes into a language is a very
serious question that needs a great discussion but one never can tell,
may be mysql developers will be interested in my proposal ;)
I think that taking into account (*) it will be relatively simply to
realise such an operator (even not embedded for a start). Or am I not
right?
signature.asc
Description: Digital signature
