Search the MySQL manual:
MySQL Manual

Buy this Reference Manual in softcover from Barnes & Noble!

/ / Up / Table of Contents

6.4.3 Синтаксис оператора INSERT 

    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
или INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
или INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression, ...

Оператор INSERT вставляет новые строки в существующую таблицу. Форма данной команды INSERT ... VALUES вставляет строки в соответствии с точно указанными в команде значениями. Форма INSERT ... SELECT вставляет строки, выбранные из другой таблицы или таблиц. Форма INSERT ... VALUES со списком из нескольких значений поддерживается в версии MySQL 3.22.5 и более поздних. Синтаксис выражения col_name=expression поддерживается в версии MySQL 3.22.10 и более поздних.

tbl_name задает таблицу, в которую должны быть внесены строки. Столбцы, для которых заданы величины в команде, указываются в списке имен столбцов или в части SET:

Если задается команда INSERT ... SELECT или INSERT ... VALUES со списками из нескольких значений, то для получения информации о данном запросе можно использовать функцию C API mysql_info(). Формат этой информационной строки приведен ниже:

Records: 100 Duplicates: 0 Warnings: 0

Duplicates показывает число строк, которые не могли быть внесены, поскольку они дублировали бы значения некоторых существующих уникальных индексов. Указатель Warnings показывает число попыток внести величину в столбец, который по какой-либо причине оказался проблематичным. Предупреждения возникают при выполнении любого из следующих условий:

Главы

User Comments

Posted by on Wednesday December 18 2002, @5:27pm [Delete] [Edit]

This page states that you can use a previously
inserted column to help define a new column, in its
example. However, it seems that auto_increment
columns aren't defined until after the rest of the
query is evaluated. This makes sense, as it wouldn't
assign an auto_increment value until the parser has
verified that the query is valid, but it means that you
can't use an auto_increment column to help define
subsequent columns. For example:

INSERT INTO `table` (id,sentence) VALUES(NULL,
concat('The id is ',id))

Even though the id column is listed first and is
evaluated first, a value is not inserted into it until the
rest of the query is evaluated, so the sentence
column would always contain the string 'The id is 0'.

Posted by Charles Gregory on Friday September 20 2002, @12:10pm [Delete] [Edit]

If you encounter "ERROR 1036: Table 'xxx' is read
only", this may be due to a corrupt internal setting in
MySQL. This can occur if, for example, you upload a
new table from a Windows-based MySQL database
directly to a Linux/Unix database. The solution is to
use 'dumpmysql' to dump the databases, THEN be
sure to DROP TABLE for the 'read only tables' or the
internal config will not be properly reset! Deleting the
table files is not sufficient. Once the tables are
dropped, feed the dump file back to mysql, and it will
create the tables anew, and they will be writable.

Posted by Joshua Mostafa on Wednesday November 13 2002, @9:17pm [Delete] [Edit]

Regarding the use of reserved words in table names:
much better than the use of backticks is the
complete avoidance of reserved word usage. The
same goes for spaces in table names (another
scenario which calls for the use of backticks):
generally a bad idea, especially when the use of
backticks could cause potentially cause collision with
their use in scripting language, eg the execution
operator in Perl or PHP (the backtick).

Posted by on Wednesday December 18 2002, @5:29pm [Delete] [Edit]

Ever wanted to copy a table record within
the same table?
Here's how to create a new copy of an existing
record for any kind of table, regardless of the
number of columns or their type.
In this example we have a table called `media` with
a number of fields which we want to duplicate, and
one, ID, containing a unique ID which must
change.
On entry to the PHP (or other language) routine we
know the ID of the record we want to copy
($oldID), and we have generated another ID for the
new record ($newID).

  1. Create a TEMPORARY table named
    by combining the ID of the record we are to copy
    ($oldID) and the name of the table. This prevents
    collisions if two people are doing the same kind of
    thing at the
    same time. This new table is populated by the
    combined SELECT statement

  2. UPDATE any fields you wish to edit in the
    temporary table (in my case, just the field called ID)

  3. INSERT the modified record from the new table
    back into the old - there's no need to
    specify 'WHERE' since this table contains just the one
    record we were working with.

  4. Drop the old table.

Code:
CREATE TEMPORARY TABLE `media_$oldID`
SELECT * FROM `media`
WHERE ID = '$oldID';

UPDATE `media_$oldID`
SET ID = '$newID';

INSERT INTO `media`
SELECT * FROM `media_$oldID`;

DROP TABLE `media_$oldID`;

Benchmarks:
To create 100 copies of a 360KB media record
(36MB data):


Hope this is of use to someone.
Posted by Steve Yates on Wednesday December 18 2002, @5:29pm [Delete] [Edit]

Perhaps it's apparent but since there's not an
example, strings must be quoted when using
VALUES. Otherwise the server evidently interprets
the values as columns since an error message is
generated: "Unknown column 'firstvalue' in 'field
list'".Numbers do not have to be
quoted. Dates may or may not require quoting
based on their format (see "6.2.2.2 The DATETIME,
DATE, and TIMESTAMP Types"). NULLs must be
indicated as "NULL" values, not left blank.

Posted by [name withheld] on Wednesday December 18 2002, @5:29pm [Delete] [Edit]

Error 1036 Table tbl_name is read only.
Here is a new one for all you who have recieved this
error running MySql as a service on windows 2000.
When running the MySql server --standalone all
tables work fine, but when installed as a service all
the tables become read-only? After many hours of
troubleshooting i relized when i decided to reload
MySql and tried to DROP a database, that MySql did
not have file write permission to the hard drive under
Win2000. The FIX: go to
start=>programs=>administrative tools=>services
and go to properties of the MySql service (once
installed) select the log-on tab and choose "Log on
as this account: and put in the administrator log-on
information (or a user with file write permissions).
The Local System Account apperentlydoes not seem
to have that permission. Anyway it worked for me if
you should have this problem give it a try.

Posted by [name withheld] on Friday March 7 2003, @1:41pm [Delete] [Edit]

maybe running a service such as mysql as administrator isn't the best idea in the world, just an opinion (theres a reason it won't run as root generally in linux ;) )

Add your own comment.

Top / / / Up / Table of Contents