mysql将列数据类型复制到另一个表 [英] mysql copy column data type to another table

问题描述

是否可以将列的结构从已填充的表复制到空的新表?我只是在问没有数据复制结构

Is there a way to copy a column's structure from an already populated table to a new table which is empty? I'm only asking about copying the structure without the data

示例: 我们有一张桌子

Example: We have a table

CREATE TABLE `animals` (
`animal` varchar(11) NOT NULL,
   `food` varchar(11) NOT NULL,
  PRIMARY KEY (`animal`)
 ) ENGINE=InnoDB

INSERT INTO `animals` (`animal`, `food`) VALUES
('cat', 'chips'),
('dog', 'bones'),
('shark', 'ppl');

还有一个名为predators的新表，我只想为其创建一列，但数据类型与动物的列类型相同.

And a new table called predators for which I want to make just one column but with the same data type as the animals' column type.

有没有一种方法可以将SHOW COLUMNS/FIELDS与CREATE TABLE结合使用，或者使用具有某种类型的列(如VARCHAR(17))创建该表，然后将其更改为与animal相同的类型专栏?

Is there a way to combine the SHOW COLUMNS/FIELDS with the CREATE TABLE or create the table with a column that has some kind of type like VARCHAR(17) and then ALTER CHANGE it to the same type as the animal column?

我知道这是一个简单的问题，但是我没有运气找到答案

I know this is a simple question but i haven't had any luck finding the answer to it

推荐答案

如果要复制数据:

INSERT INTO newTable (col1, col2) 
SELECT col1, col2 FROM otherTable

如果要复制表结构:

使用LIKE根据另一个表的定义(包括原始表中定义的任何列属性和索引)创建一个空表:

Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:

CREATE TABLE new_tbl LIKE orig_tbl;

使用与原始表相同的表存储格式版本创建副本.原始表需要SELECT特权.

The copy is created using the same version of the table storage format as the original table. The SELECT privilege is required on the original table.

文档

如果要复制结构和数据:

If you want to copy the structure and the data:

CREATE TABLE animals2 AS 
SELECT *
FROM animals ;

如果要复制不包含数据的结构(但不是所有列):

And if you want to copy the structure (but not all columns) without data:

CREATE TABLE animals2 AS 
SELECT animal                -- only the columns you want
FROM animals 
WHERE FALSE;                 -- and no data

这篇关于mysql将列数据类型复制到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！