插入数据_我的学习教程

INSERT语句

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

注

语句的INSERT ... VALUES和 INSERT ... SET形式基于明确指定的值插入行。
INSERT ... SELECT将插入从另一个表或多个表中选择的行。如果要插入的行将导致索引中的重复值，则INSERT带有ON DUPLICATE KEY UPDATE子句可以更新现有行。
如果给定INSERT语句要插入的任何行与列出的分区之一都不匹配，则该INSERT语句将失败。

示例

如果COLUMN列表和VALUES列表都为空，则INSERT创建一行并将每列设置为其默认值；严格模式下将报错

INSERT INTO tbl_name () VALUES();

表达式expr可以引用值列表中先前设置的任何列。：

您可以执行此操作，因为col1的值是在col2之前已分配
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
但是以下操作是不合法的，因为col1的值是在col2之后分配的：
INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

修饰符

HIGH_PRIORITY：它会覆盖 --low-priority-updates选项的影响(如果服务器是使用该选项启动的)。它还会导致不使用并发插入
LOW_PRIORITY：INSERT将延迟执行，直到没有其他客户端从表中读取为止。这包括在现有客户端正在阅读以及INSERT LOW_PRIORITY语句正在等待时开始阅读的其他客户端。因此，发出INSERT LOW_PRIORITY声明的客户可能要等待很长时间。LOW_PRIORITY会影响只使用表级锁的存储引擎（如 MyISAM，MEMORY，和 MERGE）。（LOW_PRIORITY通常不应与MyISAM表一起使用，因为这样做会禁用并发插入。）
IGNORE：在执行INSERT语句时发生的可忽略错误将被忽略。例如，复制表中现有UNIQUE索引或PRIMARY KEY值的行将导致重复键错误，如果不使用IGNORE该语句将中止。使用IGNORE，该行将被丢弃，并且不会发生错误。被忽略的错误会生成警告。可以使用mysql_info()确定实际向表中插入了多少行。
ON DUPLICATE KEY UPDATE：如果插入的行会出现UNIQUE索引或PRIMARY KEY中重复的值，则UPDATE会出现旧行的。如果将行作为新行插入，则每行的受影响行值为1；如果更新了现有行，则为2；如果将现有行设置为其当前值，则为0。

如果列c的值重复，那么将c的值加1在赋予列c
INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
如果列c的值重复，那么赋予列c的值为 引用的当前行的a列和b列相加值
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

LOAD DATA

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

该LOAD DATA语句以很快的速度将行从文本文件读入表中。 LOAD DATA是SELECT ... INTO OUTFILE的补充。
LOAD DATA支持使用显式分区选择，该PARTITION选项带有一个或多个以逗号分隔的分区，子分区或两者的名称列表。使用此选项时，如果无法将文件中的任何行插入列表中命名的任何分区或子分区中，则该语句将失败，
文件名必须以文字字符串形式给出。在Windows上，在路径名称中将反斜杠指定为正斜杠或两个反斜杠
如果输入文件的内容使用的字符集与默认字符集不同，通常最好使用CHARACTER SET子句指定文件的字符集。
ucs2、utf16、utf16le、utf32字符集的数据文件不能加载

修饰符

LOCAL：文件由客户端主机上的客户端程序读取并发送到服务器。可以将文件的完整路径名来指定其确切位置。如果给出为相对路径名，则相对于启动客户端程序的目录解释该名称。
- 指定LOCAL将在MySQL服务器存储临时文件的目录中创建文件的副本。该目录中副本的足够空间不足会导致LOAD DATA LOCAL语句失败。
- 使用LOAD DATA，数据解释和重复键错误终止操作。
- 使用LOAD DATA LOCAL，数据解释和重复键错误将成为警告，并且操作将继续，因为服务器无法在操作过程中停止文件的传输。对于重复键错误，这与指定IGNORE相同。
- 如果未指定LOCAL，则该文件必须位于服务器主机上，并且可以由服务器直接读取。服务器使用以下规则来定位文件：
- 如果文件名是绝对路径名，则服务器将使用给定的名称。
- 如果文件名是具有一个或多个前导组件的相对路径名，则服务器将搜索相对于服务器数据目录的文件。
- 如果给出的文件名中没有前导组件，则服务器将在默认数据库的数据库目录中查找该文件。
LOW_PRIORITY：将延迟执行，直到没有其他客户端从表中读取为止。这会影响只使用表级锁的存储引擎（例如MyISAM， MEMORY和MERGE）。
CONCURRENT：满足并发插入条件的MyISAM表，其他线程可以在LOAD DATA执行时从表中检索数据。即使没有其他线程同时使用该表，此修饰符也会影响性能。
REPLACE\IGNORE:REPLACE与IGNORE修饰符控制处理唯一键值重复现有行输入行：
- 如果指定REPLACE，则输入行将替换现有行。换句话说，主键或唯一索引的值与现有行的值相同的行。
- 如果指定IGNORE，则在唯一键值上与现有行重复的行将被丢弃
- 如果您未指定任何修饰符，则行为取决于是否指定了LOCAL修饰符。如果不使用LOCAL，则在找到重复的键值时会发生错误，而文本文件的其余部分将被忽略。使用LOCAL，默认行为与指定IGNORE的行为相同。这是因为服务器无法在操作过程中停止文件的传输。

索引处理

在执行LOAD DATA之前执行一条语句SET foreign_key_checks = 0，在LOAD DATA期间忽略外键约束
如果您LOAD DATA在空MyISAM表上使用，则所有非唯一索引都将在单独的批处理中创建（与REPAIR TABLE一样）。LOAD DATA当您有很多索引时，这样去做会更快：
在将文件加载到表中之前关闭索引ALTER TABLE ... DISABLE KEYS，并在加载文件后重新创建索引ALTER TABLE ... ENABLE KEYS。

FIELDS和LINES子句

如果同时指定了两个子句，则FIELDS必须在LINES之前
如果指定一个FIELDS子句必须指定至少一个子句（TERMINATED BY， [OPTIONALLY] ENCLOSED BY和 ESCAPED BY）。这些子句的参数只能包含ASCII字符。
对于Windows系统上生成的文本文件请使用 LINES TERMINATED BY '\r'
如果LOAD DATA的文件是通过SELECT ... INTO OUTFILE导出的，那么LOAD DATA时LOAD DATA的FIELDS和LINES 和 SELECT ... INTO OUTFILE的FIELDS和LINES必须匹配

TERMINATED 分界符字段默认为'\t' 行默认为 '\n'
FIELDS [OPTIONALLY] ENCLOSED BY 如果省略OPTIONALLY则所有字段都用ENCLOSED BY字符括起来，否则只用ENCLOSED BY字符括起数据类型为字符串的列的值(如CHAR, BINARY, TEXT, ENUM)
ESCAPED BY 转义字符
STARTING BY 去除指定的行前缀，如果没有行前缀，那么将跳过

默认值：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

含义：
在换行符处查找行边界。
不要跳过任何行前缀。
在制表符中将行拆分为字段。
不要期望字段被括在引号内。
将前面跟转义字符\的字符解释为转义序列。例如，\t、\n和\\分别表示制表符、换行符和反斜杠

IGNORE number {LINES | ROWS}

读取时跳过指定行

跳过包含列名称的初始标题行
IGNORE 1 LINES

col_name_or_user_var

如果不指定列列表将会插入所有列，否则只会插入指定列；如果输入文件中字段的顺序与表中列的顺序不同，则还必须指定列列表。否则，MySQL不能匹配输入字段和表列。
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col_name_or_user_var [, col_name_or_user_var] ...);

SET

每个col_name_or_user_var值可以是列名或用户变量。使用用户变量，可以用SET子句在将结果分配给列之前对其值执行预处理转换
SET子句中的赋值操作符的左侧只允许有列名
SET赋值的右侧使用子查询。返回要分配给列的值的子查询只能是标量子查询；不能使用子查询从正在加载的表中进行选择
被IGNORE子句忽略的行不会对列/变量列表或SET子句进行处理
当加载固定行格式的数据时，不能使用用户变量，因为用户变量没有显示宽度

将第一个输入列用作的值t1.column1，并将第二个输入列分配给用户变量，该用户变量在t1.column2用于值之前要进行除法运算：
LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

SET子句可用于提供不是从输入文件派生的值。设置column3为当前日期和时间
LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column3 = CURRENT_TIMESTAMP;

通过将输入值分配给用户变量而不将变量分配给表列来丢弃它
LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

LOAD XML

LOAD XML
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE [db_name.]tbl_name
    [CHARACTER SET charset_name]
    [ROWS IDENTIFIED BY '<tagname>']
    [IGNORE number {LINES | ROWS}]
    [(field_name_or_user_var
        [, field_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

导出为xml文件

mysql --xml -e 'SELECT * FROM mydb.mytable' > file.xml

ROWS IDENTIFIED BY

指定每行的标签：默认为<row>,<row>元素被视为等同于数据库表行
该语句支持三种不同的XML格式,三种格式都可以在同一个XML文件中使用。导入时会自动检测每一行的格式并正确解释。根据标签或属性名称以及列名称来匹配标签
列名称作为属性，列值作为属性值

<row column1="value1" column2="value2" .../>

列名作为标签，列值为标签的内容

<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>

列名是标签<field>的name属性，值是标签的内容

<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>

LOW_PRIORITY / CONCURRENT、LOCAL、REPLACE / IGNORE、CHARACTER SET、SET与LOAD DATA相同
XML文件中的每个字段不需要都与对应表中的列匹配。没有相应列的字段将被跳过
可以通过在用户变量中选择字段值，然后使用SET设置目标表的列等于这些变量的值，用户变量无需按照与相应字段相同的顺序列出或分配

LOAD XML INFILE '../bin/person-dump.xml'
    ->     INTO TABLE test.individual (@person_id, @fname, @lname, @created)
    ->     SET individual_id=@person_id, name1=@fname, name2=@lname, made=@created;

REPLACE

REPLACE [LOW_PRIORITY]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...

REPLACE [LOW_PRIORITY]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list

REPLACE [LOW_PRIORITY]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

REPLACE是对SQL标准的MySQL扩展。它要么插入，要么删除并插入。
REPLACE，必须同时拥有表的INSERT和DELETE权限
REPLACE的工作方式与INSERT完全相同，不同之处在于，如果表中的旧行与新行具有相同PRIMARY KEY或UNIQUE索引值，则在插入新行之前删除旧行。
REPLACE仅当表具有PRIMARY KEY或UNIQUE索引时才有意义。否则，它等同于INSERT，因为没有索引可用于确定新行是否重复另一行。
所有列的值均取自REPLACE语句中指定的值，所有缺少的列均设置为其默认值。就像INSERT一样。不能引用当前行中的值并在新行中使用它们
REPLACE支持使用显式分区PARTITION的关键字，该关键字带有分区，子分区或两者的逗号分隔名称列表。与INSERT相同，如果无法将新行插入到任何这些分区或子分区中，则该REPLACE语句将失败
REPLACE语句返回一个计数，以指示受影响的行数。这是删除和插入的行的总和。如果单行的计数为1，则会REPLACE插入一行，并且不会删除任何行。如果计数大于1，则在插入新行之前删除一个或多个旧行。如果表包含多个唯一索引并且新行复制了不同唯一索引中不同旧行的值，则单行可能会替换一个以上的旧行。
受影响的行数使您可以轻松确定REPLACE是仅添加一行还是也替换了任何行：检查计数是否为1（添加）或更大（已替换）。

SET

SET赋值对右侧列名的引用将被视为默认值(col_name)，因此赋值相当于SET col_name = DEFAULT(col_name) + 1

SET col_name = col_name + 1

优化

如果表中有FOREIGN KEY约束，则可以通过在导入会话期间关闭外键检查来加快表导入

SET foreign_key_checks = 0;
... SQL import statements ...
SET foreign_key_checks = 1;

对于大表，这可以节省大量磁盘I/O。

如果UNIQUE对辅助键有限制，则可以通过在导入会话期间暂时关闭唯一性检查来加快表的导入

SET unique_checks = 0;
... SQL import statements ...
SET unique_checks = 1;

对于大表，这可以节省大量磁盘I/O，因为InnoDB可以使用更改缓冲区批量写入辅助索引记录。但是要确保数据不包含重复的密钥