获取 MySQL 中现有行的插入语句

2021-12-29 00:00:00 syntax mysql

使用 MySQL 我可以运行查询:

Using MySQL I can run the query:

SHOW CREATE TABLE MyTable;

它会返回指定表的创建表语句.如果您已经创建了一个表,并且想要在另一个数据库上创建相同的表,这将非常有用.

And it will return the create table statement for the specificed table. This is useful if you have a table already created, and want to create the same table on another database.

是否可以为已经存在的行或一组行获取插入语句?有些表有很多列,如果我能够得到一个插入语句来将行传输到另一个数据库而不必写出插入语句,或者不必将数据导出到 CSV 然后导入相同的数据,那对我来说会很好进入另一个数据库.

Is it possible to get the insert statement for an already existing row, or set of rows? Some tables have many columns, and it would be nice for me to be able to get an insert statement to transfer rows over to another database without having to write out the insert statement, or without exporting the data to CSV and then importing the same data into the other database.

只是澄清一下,我想要的是如下工作方式:

Just to clarify, what I want is something that would work as follows:

SHOW INSERT Select * FROM MyTable WHERE ID = 10;

并为我返回以下内容:

INSERT INTO MyTable(ID,Col1,Col2,Col3) VALUES (10,'hello world','some value','2010-10-20');

推荐答案

似乎没有办法从 MySQL 控制台获取 INSERT 语句,但您可以使用 mysqldump 就像 Rob 建议的那样.指定 -t 以省略表创建.

There doesn't seem to be a way to get the INSERT statements from the MySQL console, but you can get them using mysqldump like Rob suggested. Specify -t to omit table creation.

mysqldump -t -u MyUserName -pMyPassword MyDatabase MyTable --where="ID = 10"

相关文章