What is the difference between create as and create like in Mysql? (Comparative introduction)

The content of this article is about the difference between create as and create like in Mysql? (Comparative introduction) has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

You may find it very convenient when using Navicat Premium, such as copying tables or data structures. In fact, this method of copying table data or structures is created table as and create table like.

Careful friends will ask, what is the difference between them? . . . Without further ado, let’s get straight to the point:,

(Recommended course: MySQL Tutorial)

For example, here is table data t1:

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID，自增',
  `uid` bigint(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户uid',
  `nickname` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '昵称',
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '用户名',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `unn`(`uid`, `name`, `nickname`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci  ROW_FORMAT = Compact;

-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES (3, 100, 'kaven', 'test3');
INSERT INTO `t1` VALUES (2, 101, 'maha', 'test2');
INSERT INTO `t1` VALUES (4, 102, 'loose', 'test4');
INSERT INTO `t1` VALUES (5, 105, 'balala', 'test5');

Attention There is an index on it:

Create As Copy Table

create table t1_copy as select * from t1 where 1=2# 或者 create table t1_copy as select * from t1 limit 0

Here we only need the table structure, so where above 1=2 or lmit 0 queries empty data. We will find that the new table t1_copy has no index:

The index information of the t1 table (source table) is missing but only the table structure is the same. (If you want to copy data, just remove limit 0, that is, copy all the queried data)

Create like Copy table

create table t1_copy2 like t1

We found that the new table created by like contains the complete table structure and index information of the source table.

Summary:

as is used to create the same table structure and copy the source table data.

like is used to create a complete table structure and all indexes.

Note: The two methods will not copy the permission settings on the table when copying the table. For example, if permissions were originally set for table A, after copying table AA, table AA does not have permissions similar to table A. Friends who are interested can try it

The above is the detailed content of What is the difference between create as and create like in Mysql? (Comparative introduction). For more information, please follow other related articles on the PHP Chinese website!