1. 常规操作
进入数据库
use market;
改变字符编码
charset utf8;
导出数据
mysql \
-h 10.0.2.54 \
-P 3307 \
-u marketuser \
-e "use market; charset utf8; select * from RPT_ONLINE_KPI;" \
-p | sed 's/\t/,/g' > ./rpt_online_kpi_2015-12-11.txt
2. DDL操作
修改表的自增id值
alter table PMS_TP_CONFIG AUTO_INCREMENT=1;
新增字段
新增的字段添加到所有字段的最后
alter table PMS_TP_CONFIG add slot_counts int NOT NULL DEFAULT 0 COMMENT '坑位总数'
在指定字段之后新增字段
alter table PMS_TP_CONFIG add slot_counts int NOT NULL DEFAULT 0 COMMENT '坑位总数' after tpa_name;
删除字段
alter table PMS_TP_CONFIG drop column slot_counts;
修改字段类型
alter table PMS_COMBINEORDER_PRODUCT_RECOMMEND
change column REC_PRODS REC_PRODS varchar(2048) not null comment '推荐商品';
建表
自增id、日期自动更新
-- Table "PMS_TP_CONFIG" DDL
DROP TABLE `PMS_TP_CONFIG`;
CREATE TABLE `PMS_TP_CONFIG` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`platform` varchar(16) NOT NULL COMMENT '终端平台',
`page_id` int(16) NOT NULL COMMENT '页面id',
`page_name` varchar(128) NOT NULL COMMENT '页面名称',
`tpa` varchar(16) DEFAULT NULL COMMENT 'tpa',
`tpa_name` varchar(128) DEFAULT NULL COMMENT 'tpa名称',
`slot_counts` int(16) NOT NULL DEFAULT 0 COMMENT '坑位总数',
`is_delete` int(4) NOT NULL DEFAULT 0 COMMENT '标识记录是否无效,0代表记录有效,1代表记录无效',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据刷新时间',
PRIMARY KEY (`id`),
KEY `page_id` (`page_id`),
KEY `tpa` (`tpa`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='精准化自动打点配置表';
建立分区表
-- Table "PMS_ONLINE_KPI_REPORT" DDL
DROP TABLE `PMS_ONLINE_KPI_REPORT`;
CREATE TABLE `PMS_ONLINE_KPI_REPORT` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`page_id` bigint(16) NOT NULL COMMENT '页面id',
`section_id` varchar(64) NOT NULL COMMENT '栏位id',
`algorithm_id` bigint(16) NOT NULL COMMENT '算法id',
`platform` varchar(16) NOT NULL COMMENT '平台',
`kpi_id` bigint(8) NOT NULL COMMENT '监控指标的类型',
`kpi_value` bigint(16) NOT NULL COMMENT '监控指标的值',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据刷新时间',
PRIMARY KEY (`id`,`update_time`),
KEY `comb` (`page_id`,`section_id`,`algorithm_id`)
)
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='精准化实时KPI指标监控表'
PARTITION BY RANGE ( UNIX_TIMESTAMP(update_time) ) (
PARTITION pmin VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-01 00:00:00') ),
PARTITION p201512 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-01-01 00:00:00') ),
PARTITION p201601 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-02-01 00:00:00') ),
PARTITION p201602 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-03-01 00:00:00') ),
PARTITION p201603 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-04-01 00:00:00') ),
PARTITION p201604 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-05-01 00:00:00') ),
PARTITION p201605 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-06-01 00:00:00') ),
PARTITION p201606 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-07-01 00:00:00') ),
PARTITION p201607 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-08-01 00:00:00') ),
PARTITION p201608 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-09-01 00:00:00') ),
PARTITION p201609 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-10-01 00:00:00') ),
PARTITION p201610 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-11-01 00:00:00') ),
PARTITION p201611 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-12-01 00:00:00') ),
PARTITION p201612 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00') ),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
3. DML操作
新增表注释
alter table pms_test COMMENT='推荐搭配表,用于详情页推荐搭配栏位';
显示表结构
show create table PMS_TP_CONFIG;
清空数据表
truncate table PMS_TP_CONFIG;
等同于delete from PMS_TP_CONFIG where 1=1;
truncate属于完全清空,操作需要谨慎
插入数据
insert into PMS_TP_CONFIG(id,platform,page_id,page_name,tpa,tpa_name,slot_counts,is_delete)
values('19','APP','20000','APP购物车页','8_1','APP购物车页_无商品时猜你喜欢','20','0');
删除数据
delete from PMS_TP_CONFIG where id in (16,17);
更新数据
update PMS_TP_CONFIG set slot_counts=10 where id = 2;
将A表数据导入导B表
INSERT INTO PMS_ONLINE_KPI_REPORT (
page_id,
section_id,
algorithm_id,
platform,
kpi_id,
kpi_value,
update_time
) SELECT
page_id,
section_id,
algorithm_id,
platform,
kpi_id,
kpi_value,
update_time
FROM
RPT_ONLINE_KPI;
索引
查询索引
show index from PMS_ONLINE_KPI_REPORT;
新增索引
新增唯一索引
alter table pms_test add unique product_id(product_id);
新增联合索引
alter table `PMS_ONLINE_KPI_REPORT` add index `comb`(`kpi_id`,`update_time`);
删除索引
drop index comb on PMS_ONLINE_KPI_REPORT;