AI 助理
备案控制台
开发者社区 数据库 文章 正文

4.2. 表管理(Table)

2018-01-05 1293
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介：

4.2.1. 数据类型

4.2.1.1. SET 集合类型

SET 集合类型，此类型适合用于多项选择场景，例如保存表单中的checkbox。

CREATE TABLE `QA` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`question` VARCHAR(255) NOT NULL COMMENT '问题描述',
	`answer` SET('A','B','C','D') NOT NULL COMMENT '问题答案',
	PRIMARY KEY (`id`)
)
COMMENT='Multiple Choice'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

插入数据

INSERT INTO `QA` (`id`, `question`, `answer`) VALUES
	(1, 'Netkiller 系列手札始于那一年？ A.2000年，B.2008年，C.2010年，D.2016年', 'A'),
	(2, 'Netkiller 系列手札有哪些? A.《Netkiller Scals 手札》, B.《Netkiller Java 手札》, C.《Netkiller Linux 手札》, D.《Netkiller EMC 手札》', 'B,C'),
	(3, 'XXXXXXXXX', 'C,D'),
	(4, 'XXXXXXXXX', 'A,B,C'),
	...
	...
	(1000, 'XXXXXXXXXX', 'B,C,D'),
	...
	...
	(5000, 'XXXXXXXXXX', 'A,B,C,D');

查询 SET 结果集，MySQL为SET配备了FIND_IN_SET函数

				
select * from QA where FIND_IN_SET('B',`answer`);

下面两种方法也能实现，但不推荐使用。

				
select question, answer from QA where locate('B',answer)>0;
select question, answer from QA where POSITION('B' in answer)>0;

查询多个答案

				
select question, answer from QA where answer = 'B,C';

4.2.2. create table ... select

创建空表

			
create table admin_user_history select * from admin_user where 1 <> 1;

创建有数据的表

			
create table admin_user_history select * from admin_user;

4.2.3. modifiy table

modifiy table

ALTER TABLE ecs_users add user_picture varchar(255);

4.2.4. TEMPORARY Table

临时表将在你连接期间存在。一旦断开时将自动删除表并释放所用的空间。你在连接期间删除该表也同样释放空间。

CREATE TEMPORARY TABLE tmp_table (
	key VARCHAR(10) NOT NULL,
	value INTEGER NOT NULL
)

声明临时表是一个HEAP表，允许你指定在内存中创建它

CREATE TEMPORARY TABLE tmp_mem_table (
	key VARCHAR(10) NOT NULL,
	value INTEGER NOT NULL
) TYPE = HEAP

4.2.5. Collate

ALTER TABLE `tmp_cats`  COLLATE='utf8_general_ci',  CONVERT TO CHARSET utf8;

4.2.6. CHARACTER

			
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8;

4.2.7. DEFAULT

更新时间

`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间',

			
CREATE TABLE `bank_account` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增唯一ID',
	`bank_name` VARCHAR(255) NOT NULL DEFAULT '0' COMMENT '银行名字(Bank Name)',
	`name` VARCHAR(50) NOT NULL DEFAULT '0' COMMENT '帐号名称(Name on account)',
	`account_number` VARCHAR(50) NOT NULL DEFAULT '0' COMMENT '银行帐号(Account Number)',
	`branch_location` VARCHAR(255) NOT NULL DEFAULT '0' COMMENT '支行位置(Branch Location)',
	`description` VARCHAR(255) NOT NULL DEFAULT '0' COMMENT '银行描述',
	`status` ENUM('Y','N') NOT NULL DEFAULT 'N' COMMENT '银行帐号状态',
	`ctime` DATETIME NOT NULL COMMENT '创建时间',
	`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间',
	PRIMARY KEY (`id`)
)
COMMENT='银行帐号'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2;

4.2.7.1. AUTO_INCREMENT

定义 AUTO_INCREMENT 起始值

CREATE TABLE `bank_account` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增唯一ID',
	`name` VARCHAR(50) NOT NULL DEFAULT '0' COMMENT '帐号名称(Name on account)',
	PRIMARY KEY (`id`)
)
COMMENT='银行帐号'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2;

设置 AUTO_INCREMENT

ALTER TABLE `accounts`
	AUTO_INCREMENT=792257;

4.2.7.2. 表存储位置(DATA DIRECTORY)

				
CREATE TABLE IF NOT EXISTS `tab_name` (
  `id` int(11) DEFAULT NULL,
  `purchased` date DEFAULT NULL,
  KEY `Index 1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (YEAR(purchased))
(PARTITION p0 VALUES IN (1990) DATA DIRECTORY = '/www/data' ENGINE = InnoDB) */;

4.2.8. KEY

4.2.8.1. PRIMARY KEY

一般主键定义

PRIMARY KEY (`id`),

复合主键

PRIMARY KEY (`id`, `user_id`),

4.2.9. COMMENT

ALTER TABLE `neo`.`stuff` COMMENT = '用户表' ;
ALTER TABLE `neo`.`stuff` CHANGE COLUMN `name` `name` VARCHAR(50) NULL DEFAULT NULL COMMENT '姓名'  ;
ALTER TABLE `neo`.`stuff` CHANGE COLUMN `password` `password` VARCHAR(50) NULL DEFAULT NULL COMMENT '用户密码' ;


CREATE TABLE `stuff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT ''姓名'',
  `password` varchar(50) DEFAULT NULL COMMENT ''用户密码'',
  `created` date NOT NULL DEFAULT ''0000-00-00'',
  PRIMARY KEY (`id`,`created`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COMMENT=''用户表''
/*!50100 PARTITION BY HASH (year(created))
PARTITIONS 10 */

4.2.10. Engine 存储引擎

4.2.10.1. 显示当前数据库支持引擎

		
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

4.2.10.2. 切换引擎

修改与切换引擎

		
ALTER TABLE `test` ENGINE=BLACKHOLE;
ALTER TABLE `test` ENGINE=InnoDB;

4.2.10.3. FEDERATED

启用 FEDERATED 引擎, 服务器环境 Ubuntu 13.04

$ sudo vim /etc/mysql/conf.d/federated.cnf
[mysqld]
federated

$ sudo service mysql restart
		
		
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

A 服务器

CREATE TABLE `t1` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	`sex` ENUM('Y','N') NULL DEFAULT 'Y',
	`passwd` VARCHAR(50) NULL DEFAULT NULL,
	`ctime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
	`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4;

B 服务器

DROP TABLE `users`;

CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	`sex` ENUM('Y','N') NULL DEFAULT 'Y',
	`ctime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
	`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
) ENGINE=FEDERATED connection = 'mysql://www:qwer123@192.168.2.1:3306/test/t1';

上面字段描述是你需要的字段，并非所有字段。这里屏蔽了passwd字段

[提示] 提示

connection = 'mysql://用户名:密码@主机:端口/数据库/表名'

 
		
mysql> DROP TABLE `users`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `users` (
    -> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `name` VARCHAR(50) NOT NULL,
    -> `sex` ENUM('Y','N') NULL DEFAULT 'Y',
    -> `ctime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> `mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=FEDERATED connection = 'mysql://www:qwer123@192.168.2.1:3306/test/t1';
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

mysql> desc users;
+-------+------------------+------+-----+---------------------+-----------------------------+
| Field | Type             | Null | Key | Default             | Extra                       |
+-------+------------------+------+-----+---------------------+-----------------------------+
| id    | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| name  | varchar(50)      | NO   |     | NULL                |                             |
| sex   | enum('Y','N')    | YES  |     | Y                   |                             |
| ctime | timestamp        | NO   |     | 0000-00-00 00:00:00 |                             |
| mtime | timestamp        | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+-------+------------------+------+-----+---------------------+-----------------------------+
5 rows in set (0.00 sec)

mysql> select * from users;
+----+------+------+---------------------+---------------------+
| id | name | sex  | ctime               | mtime               |
+----+------+------+---------------------+---------------------+
|  1 | neo  | Y    | 0000-00-00 00:00:00 | 2013-05-17 18:05:09 |
|  2 | zen  | Y    | 0000-00-00 00:00:00 | 2013-05-17 18:05:11 |
|  3 | lily | N    | 0000-00-00 00:00:00 | 2013-05-17 18:05:22 |
+----+------+------+---------------------+---------------------+
3 rows in set (0.01 sec)
[注意] FEDERATED 与 mysqldump 问题！

切记，mysqldump 只会dump出使用FEDERATED引擎表的结构,不会包含数据。

4.2.10.4. BLACKHOLE

CREATE TABLE test(id INT, val CHAR(10)) ENGINE = BLACKHOLE;

4.2.10.5. ARCHIVE

归档(是适用于存放大量数据的存储引擎), 仅支持select、insert操作; 不支持delete 、update、索引等操作；使用zlib无损算法压缩数据，节省磁盘空间；

适用场景：适用于大量可查询但不能删除的历史数据保存；

基于 order 表创建 order_audit 归档表

		
create table order_audit engine=archive as select * from `order`;

order_audit 表结构如下

		
CREATE TABLE `order_audit` (
  `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单ID',
  `name` varchar(45) NOT NULL COMMENT '订单名称',
  `price` float NOT NULL COMMENT '价格',
  `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8		
		
		
		
mysql> show table status like 'order_audit';
+-------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name        | Engine  | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+-------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-----------------+----------+----------------+---------+
| order_audit | ARCHIVE |      10 | Compressed |    4 |           2215 |        8861 |               0 |            0 |         0 |           NULL | NULL        | 2017-11-16 17:30:34 | NULL       | utf8_general_ci |     NULL |                |         |
+-------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.01 sec)

4.2.10.6. CSV

创建表

		
CREATE TABLE `csv_table` (
  `id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8

查看表状态

		
mysql> show table status like 'csv_table';
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| csv_table | CSV    |      10 | Dynamic    |    2 |              0 |           0 |               0 |            0 |         0 |           NULL | NULL        | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

插入数据

		
insert into csv_table values (1,'Neo',37),(2,'Jam',40);

查看数据

		
mysql> SELECT * FROM test.csv_table;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Neo  |  37 |
|  2 | Jam  |  40 |
+----+------+-----+
2 rows in set (0.00 sec)

CSV 引擎是可以直接将csv文件复制出来的，表存储在 /var/lib/mysql/ 目录

		
root@netkiller /etc/nginx/conf.d % ls -1 /var/lib/mysql/test/csv*
/var/lib/mysql/test/csv_table.CSM
/var/lib/mysql/test/csv_table.CSV
/var/lib/mysql/test/csv_table.frm

.*CSM, *.frm 是表结构文件，*.CSV 是我们需要的文件，纯文本，可以使用Excel打开。

		
root@netkiller /etc/nginx/conf.d % cat /var/lib/mysql/test/csv_table.CSV
1,"Neo",37
2,"Jam",40





原文出处：Netkiller 系列 手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。

文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
存储
数据安全/隐私保护
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
玄学酱
目录
相关文章
c清风莫追
|
6月前
|
搜索推荐
舒尔特表练习记
舒尔特表练习记 1 练习的开始
c清风莫追
60 0
听风de歌
|
关系型数据库 MySQL 数据库
对于mysql数据库delect from,truncate table,drop table, update操作的时候如何选择
对于mysql数据库delect from,truncate table,drop table, update操作的时候如何选择
听风de歌
185 2
游客qsxez56gggwqy
|
1月前
ascll表
ascll表
游客qsxez56gggwqy
24 0
1880486840013302
|
SQL 存储 关系型数据库
PostgreSQL 动态表复制（CREATE TABLE AS & CREATE TABLE LIKE）
PostgreSQL 动态表复制（CREATE TABLE AS & CREATE TABLE LIKE）
1880486840013302
691 0
-编程工程师-
|
消息中间件 安全 Dubbo
程序员健康作息表
程序员健康作息表
-编程工程师-
144 0
Iric
|
Java 索引
Table（表）
Table（表）
Iric
85 0
明金同学
|
关系型数据库 MySQL 数据库
MySQL创建数据库 easyShopping，包括area表、goods表、customer表、orders表、ordersdetall表、test表
MySQL创建数据库 easyShopping，包括area表、goods表、customer表、orders表、ordersdetall表、test表
明金同学
949 0
MySQL创建数据库 easyShopping，包括area表、goods表、customer表、orders表、ordersdetall表、test表
咖啡机（K.F.J）
|
Web App开发 前端开发 搜索推荐
关于table的一些记录
HTML有10个表格相关标签
咖啡机（K.F.J）
164 0
关于table的一些记录
学堂小助手
|
Java 关系型数据库 MySQL
添加和删除数据表(table)|学习笔记
快速学习添加和删除数据表(table)
学堂小助手
164 0
libydwei2019
|
SQL 索引
Can you create an index on a foreign table?(外部表可以创建索引吗？)
来自google搜索的一段回答 No, you will get an error: ERROR: cannot create index on foreign table "tablename" ********** Error ********** ERROR: cannot create...
libydwei2019
1466 0

热门文章

最新文章

  • 1
    weex-html5 组件进阶
  • 2
    会声会影2023视频编辑软件免费序列号永久
  • 3
    苹果电脑 Mac OS X 系统上防止误按 command+Q 退出软件
  • 4
    SSH连接远程服务器，本地known_hosts文件记录了什么
  • 5
    31.6. MongoDB
  • 6
    saltstack的探索-改善管理用户的sls文件
  • 7
    marathon小知识点分享之如何远程调试marathon
  • 8
    Myeclipse 操作数据库
  • 9
    光伏发电或能控制城市天气
  • 10
    WPF进度条
  • 1
    ROS机器视觉入门：从基础到人脸识别与目标检测
    83
  • 2
    鸿蒙开发：自定义一个简单的标题栏
    27
  • 3
    鸿蒙开发：切换至基于rcp的网络请求
    28
  • 4
    【MyBatisPlus·最新教程】包含多个改造案例，常用注解、条件构造器、代码生成、静态工具、类型处理器、分页插件、自动填充字段
    31
  • 5
    《C++跨平台编译：打破系统边界，释放代码潜能》
    23
  • 6
    电路板设计中射频反馈线的优化
    10
  • 7
    《C++20 图形界面程序：速度与渲染效率的双重优化秘籍》
    10
  • 8
    图解 Spring 循环依赖，一文吃透！
    18
  • 9
    关于 Kafka 高性能架构，这篇说得最全面，建议收藏！
    10
  • 10
    微服务和 SOA 的 6 大核心区别，你都知道吗？
    14

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册

    • 相关实验场景

    更多
  • 使用SQL语句实现数据表管理
  • 使用SQL语句管理索引
  • SQL进阶之约束、索引
    • 下一篇
    阿里云OSS设置跨域访问