创建表(表数据类型、表数据类型选项、表选项、列选项、if not exists选项)、临时表操作

本文详细介绍了MySQL中创建表的要素,包括数据类型如整数、定点小数和浮点数、字符串类型,以及日期/时间类型。此外,还讲解了数据类型选项,如unsigned和zerofill,列选项如默认值和注释,表选项如存储引擎和自增列设置,以及if not exists和临时表的概念。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

  对于一张表来说,最基本的是列标题,列的数据类型。通过这二者可以建最基本的表,在此基础上还可以延伸出很多选项

一、列的数据类型(常用)

 

一、数值类型

1.整数类型(M表示最小显示宽度,将存储值超过显示宽度,显示宽度会自动增加)

1.INT[(M)] [UNSIGNED] [ZEROFILL]

The signed range is -2147483648 to 2147483647.

The unsigned range is 0 to 4294967295.

2.TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very small integer. The signed range is -128 to 127. The unsigned

range is 0 to 255.

3.SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

A small integer. The signed range is -32768 to 32767. The unsigned

range is 0 to 65535.

4.BIGINT[(M)] [UNSIGNED] [ZEROFILL]

A large integer. The signed range is -9223372036854775808 to

9223372036854775807. The unsigned range is 0 to 18446744073709551615.

5.MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

medium-sized integer. The signed range is -8388608 to 8388607. The

unsigned range is 0 to 16777215.

举例:

mysql> create table t1(id int(3));------------显示宽度是3位

Query OK, 0 rows affected (0.09 sec)



mysql> insert into t1 values(3),(30),(3000),(3000.5),(3000.4);

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from t1;

+------+

| id |

+------+

| 3 |

| 30 |

| 3000 |

| 3001 |

| 3000 |

+------+

5 rows in set (0.00 sec)

 

2.定点小数类型(M表示最多能存的总长度,D表示小数点的位数,可以存储D位小数的M位数,最多是小数位是30的65位数,默认是存储小数位是0的10位数)

只想要小数:M=D;

只想要整数:D=0;允许插入小数,但是会对第一位小数四舍五入取整数

小数位固定,不会存储近似值

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

举例:

mysql> create table t2(id decimal(4,2));

Query OK, 0 rows affected (0.04 sec)

mysql> insert into t2 values(111);------------------------------整数位数只能小于等于2

ERROR 1264 (22003): Out of range value for column 'id' at row 1



mysql> insert into t2 values(11),(11.55),(11.45),(11.456),(11.554);

Query OK, 5 rows affected, 2 warnings (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 2



mysql> select * from t2;

+-------+

| id |

+-------+

| 11.00 |

| 11.55 |

| 11.45 |

| 11.46 |------------------允许插入小数位多于三位的数,但是多出来的部分,取第一位去四舍五入

| 11.55 |

+-------+

3.浮点小数类型

小数位数不固定,假设超出存储范围,近似值进行存储

float(0)-float(24) 精确存储6位

float(25)-float(53) 精确存储15位

1.FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] (单精度浮点类型)数据精确到小数点后7位,对第8位进行四舍五入

2.DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] (双精度浮点类型)数据精确到小数点后15位,

 

二、字符串类型(M表示最多存的字符数目,汉字一般占三个字节,字母一般占一个字节)

 

文本字符串

1.char[(length)]:固定长度字符串。值少于长度则在尾部自动填充空格。length的单位是字符(当长度为0时,只能插入null值或者空字符串)

同义词:text

2.varchar[(length)]:可变长度字符串,取值是0-65535

3.longvarchar:0-(2^24-1)

同义词:mediumtext

 

枚举类型(enum、set)-----------只能是字符串类型的

当列使用枚举时,注意事项:排序问题

 

三、.日期/时间类型

1.date: 3字节

2.time: 3字节

3.datetime: 8字节

4.year: 1字节

5.timestamp: 4字节

 

二、添加数据类型选项(可以通过制定一个数据类型选项,以改变数据类型的属性和功能)

对于字符串类型,可以添加两个类型选项:character set和collate

对于bit之外的所有数字类型,可以添加一个或几个类型选项:unsigned、zerofill、auto_increment、serial default value(是非空、自增、唯一键的集合缩写)

1.unsigned:无符号的,表示只显示正数

2.zerofill:填充0

影响数字的显示方式:如果一个数字的宽度小于所允许的最大宽度,这个值前面会用0填充,如果声明了zerofill,该列会自动设置unsigned

3.auto_increment:自动增长。只适应于整数类型。一个表中只有一个列可以是自动增长的。

 

mysql> show variables like '%increment%';

+-----------------------------+-------+

| Variable_name | Value |

+-----------------------------+-------+

| auto_increment_increment | 1 |--------------------每次增长间隔是多少(自己设定变量)

| auto_increment_offset | 1 | --------------------从那个数字开始增长

| div_precision_increment | 4 |

| innodb_autoextend_increment | 64 |

+-----------------------------+-------+

 

可以通过设定两个系统变量来改变默认行为:

1、set @@auto_incerment_increment=

2、set @@auto_increment_offset=

 

举例

mysql> create table test(id int(5) zerofill,id1 int unsigned,id3 int unsigned auto_increment primary key );



1.会自动记住最大值,当再次向该列输入而没有赋值时,就用最大值+1(即使删除了最大值那一行,也会记住最大值是多少)

2.当自己输入时,可以不按照排序一个一个输入,前面值是2,后面一个值也可以输入10(但是如果是自动增长的---是主键的,自己插入的值一定不能跟表内存在的值重复)





mysql> insert into test values(1,1001,1);

Query OK, 1 row affected (0.00 sec)



mysql> insert into test values(6,1006,6);

Query OK, 1 row affected (0.01 sec)



mysql> insert into test values(7,1007,null);

Query OK, 1 row affected (0.00 sec)



mysql> select * from test;

+-------+------+-----+

| id | id1 | id3 |

+-------+------+-----+

| 00001 | 1001 | 1 |

| 00006 | 1006 | 6 |

| 00007 | 1007 | 7 |

+-------+------+-----+



删除表中id3列最大的一个值

mysql> delete from test where id3=7;

Query OK, 1 row affected (0.00 sec)



mysql> insert into test values(10,1010,null);

Query OK, 1 row affected (0.00 sec)



mysql> select * from test;

+-------+------+-----+

| id | id1 | id3 |

+-------+------+-----+

| 00001 | 1001 | 1 |

| 00006 | 1006 | 6 |

| 00010 | 1010 | 8 |

+-------+------+-----+

 

 

 

三、列选项:

 

定义列时,除了指定列名字、数据类型、大小之外,还可以制定列选项

 

1、给列指定默认值

注意:当给表指定默认值时,如果每插入一行数据,而默认值那一列的数据没有指定时,会自动插入默认值,因此可能会大量插入默认值而导致出错

1、也可以在insert和update语句中使用default关键字显式的给列赋默认值

update t1 set sex=default;

mysql> insert into t1 values(1,default);

Query OK, 1 row affected (0.00 sec)



mysql> select * from t1;

+------+------+

| id | sex |

+------+------+

| 1 | 男 |

+------+------+

1 row in set (0.00 sec)



mysql> insert into t1(sex) values('女');

Query OK, 1 row affected (0.00 sec)



mysql> select * from t1;

+------+------+

| id | sex |

+------+------+

| 1 | 男 |

| NULL | 女 |

+------+------+

2 rows in set (0.00 sec)

 

2、函数default(column)可以得到一个列的函数值


 

mysql> create table t1(id int,birth date default '20190319');

Query OK, 0 rows affected (0.05 sec)



mysql> insert into t1 values(1,default);

Query OK, 1 row affected (0.00 sec)



mysql> insert into t1 values(2,default(birth));

Query OK, 1 row affected (0.00 sec)



mysql> select * from t1;

+------+------------+

| id | birth |

+------+------------+

| 1 | 2019-03-19 |

| 2 | 2019-03-19 |

+------+------------+

2 rows in set (0.00 sec)

 

2、comment

给列添加注释。最多255个字符,注释会保存到数据字典中

 

四、表选项:

 

1、engine:指定表使用的存储引擎

存储引擎决定了该数据如何存储以及如何访问,还有事务如何处理

2、auto_increment:该选项决定了向表中插入第一行时,自增列得到的第一个值是多少

mysql> create table t1(id int auto_increment primary key) auto_increment=10;-----------------从10开始增长

mysql> insert into t1 values(null);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;

+----+

| id |

+----+

| 10 |

| 11 |

+----+

3、comment:给表添加注释(查询information_schema.tables)

select table_name,table_comment from information_schema.tables where table_name= and table_schema;

 

 

五、if not exists选项

当创建表时,如果表已经存在,则Mysql会返回出错消息。添加 if not exist选项,则强制不显示出错消息。语句执行失败


 

mysql> create table stu(id int,name varchar(20));

ERROR 1050 (42S01): Table 'stu' already exists



mysql> create table if not exists stu(id int,name varchar(20)); --------没有报错,但是语句也没有执行

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;

+-------+------+----------------------------+

| Level | Code | Message |

+-------+------+----------------------------+

| Note | 1050 | Table 'stu' already exists



六、创建临时表

临时表是一张表,用来临时保存一些数据,消耗资源非常小。它只对创建它的用户可见。当会话结束时,Mysql自动删除临时表

临时表:消耗资源非常小(在建立临时表、删除临时表以及里面的数据时)


 

create temporary table table_name();





mysql> create table t1(id int,name varchar(10));------------创建普通表t1

Query OK, 0 rows affected (0.03 sec)



mysql> create temporary table t1(id int);------------创建临时表t1

Query OK, 0 rows affected (0.00 sec)

mysql> desc t1;-------------------------------------------在使用的时候,用的首先是临时表t1

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)















 

<think>好的,我现在要解决用户的问题:如何将大量数据从临时表导入到另一个中。用户使用的是SQL Server,所以需要编写相应的SQL代码。让我先回忆一下相关的知识,然后一步步分析可能的解决方案。 首先,用户提到的是“临时表”,我需要确认这里的临时表是本地临时表(以#开头的)还是全局临时表(以##开头的),或者是变量。不过通常情况下,临时表指的是本地或全局临时表,而变量的处理可能有所不同。不过,无论是哪种,基本的插入操作应该是类似的,但需要确认临时表的范围和存在时间。 接下来,用户需要导入大量数据。对于大数据量的操作,直接使用INSERT INTO可能效率不高,特别是在事务日志和锁定方面可能会有问题。因此需要考虑更高效的方法。 可能的步骤包括: 1. 使用INSERT INTO SELECT语句:这是最直接的方法,适用于数据量不是特别大的情况。但如果数据量非常大,可能会导致事务日志增长和长时间锁定目标。 2. 使用SELECT INTO:这种方法会创建,但用户的需求是将数据导入到另一个已存在的中,所以可能不适用,除非目标可以重新创建,但通常这不是用户想要的。 3. 批量插入操作,比如使用BULK INSERT,但BULK INSERT通常用于从文件导入数据,而不是从另一个,所以可能不适用。 4. 使用分区切换(Partition Switching),这需要目标是分区,并且数据符合分区的条件,这可能是一个高效的方案,但需要预先设置分区,可能不适用于所有情况。 5. 使用BCP工具或SSIS包导出和导入数据,但这些属于外部工具,用户要求的是使用代码操作,所以可能不在考虑范围内。 6. 使用MERGE语句,如果需要有条件地插入或更新,但如果是单纯的插入,可能没有必要。 另外,还需要考虑事务的处理,比如是否需要在事务中执行,以及是否可以使用批量操作减少日志开销。SQL Server中的INSERT INTO WITH (TABLOCK)提示可以提高大容量插入的性能,同时使用批量提交(比如每1000行提交一次)来减少日志压力。 此外,索引的影响:如果目标有索引,插入大量数据时,逐行插入会导致索引维护开销大。可以考虑在插入前删除非聚集索引,插入后再重建,但需要权衡是否可行。 临时表的数据是否在同一个数据库,是否需要跨数据库访问,如果是在同一实例中,可以使用全限定名称;如果跨服务器,可能需要链接服务器或OPENROWSET等。 现在,综合这些考虑,最基础的方案是使用INSERT INTO SELECT语句,但对于大量数据可能需要优化。例如: - 使用批量操作,分批插入,比如每次插入10000行,减少事务大小。 - 使用TABLOCK提示来减少锁竞争和日志开销。 - 如果目标是空的或可以清空,使用TRUNCATE TABLE后再插入可能更快,但用户的情况可能是追加数据,所以不能截断。 可能的代码结构: 基础版本: ```sql INSERT INTO 目标 (1, 2, ...) SELECT 1, 2, ... FROM 临时表; ``` 优化版本,使用批量提交: ```sql DECLARE @BatchSize INT = 10000; WHILE 1 = 1 BEGIN INSERT INTO 目标 WITH (TABLOCK) (1, 2, ...) SELECT TOP (@BatchSize) 1, 2, ... FROM 临时表 WHERE 某种条件来分批处理,比如未处理的标志或主键范围; IF @@ROWCOUNT < @BatchSize BREAK; END; ``` 不过这里可能需要一个条件来分批获取数据,比如临时表中有自增ID或者可以分页的字段。如果临时表没有这样的字段,可能需要用ORDER BY和OFFSET FETCH,但效率可能不高。或者,如果数据可以一次性插入,但用批量选项。 另一种方法是使用SSIS或BCP,但用户要求的是代码实现,所以可能不适用。 另外,考虑目标的锁问题,使用TABLOCK提示可以让插入操作获取大容量更新锁,减少锁竞争,提高速度。 如果目标有聚集索引,按聚集索引的顺序插入可能更高效,但需要临时表中的数据已经按该顺序排序。 此外,如果临时表和目标的结构完全相同,可以使用INSERT INTO ... SELECT * FROM ...,否则需要指定名。 还需要注意事务日志的大小,确保数据库的恢复模式是否允许最小化日志操作。如果数据库处于简单恢复模式,并且使用TABLOCK提示,可能可以进行最小日志记录,减少日志增长。 总结步骤: 1. 确认临时表和目标的结构,确保对应正确。 2. 如果数据量极大,考虑分批插入,使用循环和批量大小。 3. 使用WITH (TABLOCK)提示提高插入性能。 4. 考虑禁用目标的索引,插入完成后重建。 5. 确保事务日志有足够空间,或者调整恢复模式(需谨慎,可能影响备份策略)。 可能的代码示例: 假设临时表是#TempTable,目标是dbo.TargetTable,且对应。 简单插入: ```sql INSERT INTO dbo.TargetTable (Col1, Col2, Col3) SELECT ColA, ColB, ColC FROM #TempTable; ``` 批量插入,使用分批处理: ```sql DECLARE @BatchSize INT = 10000; DECLARE @RowsAffected INT = @BatchSize; WHILE @RowsAffected = @BatchSize BEGIN INSERT INTO dbo.TargetTable WITH (TABLOCK) (Col1, Col2, Col3) SELECT TOP (@BatchSize) ColA, ColB, ColC FROM #TempTable WHERE NOT EXISTS (SELECT 1 FROM dbo.TargetTable WHERE TargetTable.PrimaryKey = #TempTable.PrimaryKey); -- 假设需要避免重复 SET @RowsAffected = @@ROWCOUNT; END; ``` 不过这个例子中的WHERE子句可能需要根据具体情况调整,比如按某个递增的键分批,或者使用OFFSET FETCH。如果临时表没有唯一标识,可能需要其他方法分页。 另一个方法是使用CTE和ROW_NUMBER来分页,但效率可能较低,不推荐用于极大数据集。 或者,如果临时表有自增,可以记录每次处理的最大ID,下次从该ID之后开始。 例如: ```sql DECLARE @BatchSize INT = 10000; DECLARE @MaxID INT = 0; DECLARE @CurrentMaxID INT; SELECT @CurrentMaxID = MAX(ID) FROM #TempTable; WHILE @MaxID < @CurrentMaxID BEGIN INSERT INTO dbo.TargetTable WITH (TABLOCK) (Col1, Col2, Col3) SELECT ColA, ColB, ColC FROM #TempTable WHERE ID > @MaxID AND ID <= @MaxID + @BatchSize; SET @MaxID = @MaxID + @BatchSize; END; ``` 这需要临时表有一个递增的ID,并且有索引,以提高查询效率。 此外,还可以考虑使用SET ROWCOUNT来限制每批的行数,但SET ROWCOUNT在后续版本中可能被废弃,推荐使用TOP。 关于性能优化,还可以考虑以下方面: - 在插入前禁用目标的非聚集索引,插入后重建。 - 使用快照隔离级别或读提交快照来减少锁争用。 - 调整LOCK_ESCALATION选项为目标TABLE,避免锁升级到页或行,但需要根据具体情况决定。 如果目标是堆(没有聚集索引),插入速度可能更快,但查找数据时会较慢,需要权衡。 最后,需要测试不同的批量大小,找到最优值,通常1000到10000行之间,但具体取决于数据行的大小和服务器配置。 总结,用户的问题可以通过基础的INSERT INTO SELECT解决,但大量数据时需要优化,如分批处理、使用TABLOCK提示、管理索引和事务日志等。</think>在 SQL Server 中,将临时表的大量数据导入另一张的核心方法是使用 `INSERT INTO SELECT` 语句。以下是分步实现及优化方案: --- ### **基础实现步骤** #### 1. 确认结构匹配 确保临时表与目标数量、数据类型和顺序一致。例如: - 临时表:`#TempTable (ID INT, Name NVARCHAR(50), Value DECIMAL(10,2))` - 目标:`dbo.TargetTable (ID INT, Name NVARCHAR(50), Value DECIMAL(10,2))` #### 2. 直接插入数据 使用基础 `INSERT INTO SELECT` 语句: ```sql INSERT INTO dbo.TargetTable (ID, Name, Value) SELECT ID, Name, Value FROM #TempTable; ``` --- ### **优化大量数据插入** #### 1. 批量插入(减少事务日志压力) 通过分批次插入,避免单次事务过大: ```sql DECLARE @BatchSize INT = 10000; -- 每批插入的行数 DECLARE @RowsAffected INT = @BatchSize; WHILE @RowsAffected = @BatchSize BEGIN INSERT INTO dbo.TargetTable WITH (TABLOCK) (ID, Name, Value) SELECT TOP (@BatchSize) ID, Name, Value FROM #TempTable ORDER BY ID; -- 假设按ID排序分批 SET @RowsAffected = @@ROWCOUNT; END; ``` #### 2. 使用 `TABLOCK` 提示(加速插入) ```sql INSERT INTO dbo.TargetTable WITH (TABLOCK) (ID, Name, Value) SELECT ID, Name, Value FROM #TempTable; ``` - `TABLOCK` 会获取级锁,减少锁竞争并可能触发最小日志记录(需数据库处于简单恢复模式)。 #### 3. 禁用非聚集索引(可选) 插入前禁用目标的非聚集索引,完成后再重建: ```sql -- 禁用索引 ALTER INDEX IX_TargetTable_Name ON dbo.TargetTable DISABLE; -- 插入数据 INSERT INTO dbo.TargetTable ... SELECT ... FROM #TempTable; -- 重建索引 ALTER INDEX IX_TargetTable_Name ON dbo.TargetTable REBUILD; ``` --- ### **高级场景** #### 1. 使用 `MERGE` 语句(有条件插入/更新) 如果需根据键值判断插入或更新: ```sql MERGE INTO dbo.TargetTable AS t USING #TempTable AS s ON t.ID = s.ID WHEN NOT MATCHED THEN INSERT (ID, Name, Value) VALUES (s.ID, s.Name, s.Value); ``` #### 2. 分区切换(仅限分区) 若目标是分区,可直接切换分区(最快方法): ```sql ALTER TABLE #TempTable SWITCH TO dbo.TargetTable PARTITION 1; ``` --- ### **注意事项** 1. **事务管理**:默认情况下,`INSERT` 是自动提交的。如需手动控制,用 `BEGIN TRANSACTION` 和 `COMMIT`。 2. **日志空间**:确保事务日志足够大,或使用简单恢复模式。 3. **临时表类型**: - 本地临时表 (`#TempTable`):仅当前会话可见。 - 全局临时表 (`##TempTable`):所有会话可见。 - 变量 (`@TableVar`):适用于小数据量。 --- ### **完整示例** ```sql -- 创建临时表 CREATE TABLE #TempTable ( ID INT PRIMARY KEY, Name NVARCHAR(50), Value DECIMAL(10,2) ); -- 假设临时表已填充数据... -- 批量插入到目标 DECLARE @BatchSize INT = 10000; DECLARE @MaxID INT = 0; DECLARE @CurrentMaxID INT; SELECT @CurrentMaxID = MAX(ID) FROM #TempTable; WHILE @MaxID < @CurrentMaxID BEGIN INSERT INTO dbo.TargetTable WITH (TABLOCK) (ID, Name, Value) SELECT ID, Name, Value FROM #TempTable WHERE ID > @MaxID AND ID <= @MaxID + @BatchSize; SET @MaxID = @MaxID + @BatchSize; END; ``` --- 通过以上方法,可高效实现从临时表导入大量数据到目标。根据实际场景选择合适的优化策略。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值