drop database if exists moodle19;
Warnings:
Note	1008	Can't drop database 'moodle19'; database doesn't exist
create database moodle19;
use moodle19;
CREATE TABLE `mdl_course_modules` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`course` bigint(10) NOT NULL DEFAULT '0',
`module` bigint(10) NOT NULL DEFAULT '0',
`instance` bigint(10) NOT NULL DEFAULT '0',
`section` bigint(10) NOT NULL DEFAULT '0',
`idnumber` varchar(100) DEFAULT NULL,
`added` bigint(10) NOT NULL DEFAULT '0',
`delay` varchar(10) NOT NULL DEFAULT '0',
`score` smallint(4) NOT NULL DEFAULT '0',
`indent` mediumint(5) NOT NULL DEFAULT '0',
`visible` tinyint(1) NOT NULL DEFAULT '1',
`checkboxesforprereqs` tinyint(1) NOT NULL DEFAULT '0',
`stylewhencomplete` varchar(200) DEFAULT '',
`checkboxforcomplete` tinyint(1) NOT NULL DEFAULT '0',
`stylewhenlocked` varchar(200) DEFAULT 'locked',
`visiblewhenlocked` tinyint(1) NOT NULL DEFAULT '1',
`visibleold` tinyint(1) NOT NULL DEFAULT '1',
`groupmode` smallint(4) NOT NULL DEFAULT '0',
`groupingid` bigint(10) NOT NULL DEFAULT '0',
`groupmembersonly` smallint(4) NOT NULL DEFAULT '0',
`completion` tinyint(1) NOT NULL DEFAULT '0',
`completiongradeitemnumber` bigint(10) DEFAULT NULL,
`completionview` tinyint(1) NOT NULL DEFAULT '0',
`completionexpected` bigint(10) NOT NULL DEFAULT '0',
`availablefrom` bigint(10) NOT NULL DEFAULT '0',
`availableuntil` bigint(10) NOT NULL DEFAULT '0',
`showavailability` tinyint(1) NOT NULL DEFAULT '0',
`showdescription` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `mdl_courmodu_vis_ix` (`visible`),
KEY `mdl_courmodu_cou_ix` (`course`),
KEY `mdl_courmodu_mod_ix` (`module`),
KEY `mdl_courmodu_ins_ix` (`instance`),
KEY `mdl_courmodu_idncou_ix` (`idnumber`,`course`),
KEY `mdl_courmodu_gro_ix` (`groupingid`)
) ENGINE=InnoDB AUTO_INCREMENT=447023 DEFAULT CHARSET=utf8 COMMENT='course_modules table retrofitted from MySQL';
# Inserting 2701 rows into the table...
ALTER TABLE moodle19.mdl_course_modules ADD stefantest LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci AFTER showdescription;
drop database moodle19;
use test;
CREATE TABLE `w_findispmon05u` (
`atpkey` INT(11) NOT NULL DEFAULT '0',
`atzo05` INT(11) NULL DEFAULT NULL,
`pos` BIGINT(21) NULL DEFAULT NULL,
`f5BnvB` INT(9) NULL DEFAULT NULL,
`f5atbvb` INT(11) NULL DEFAULT NULL,
`f5atbwmg` INT(11) NULL DEFAULT NULL,
`f5pBneu` BIGINT(12) NULL DEFAULT NULL,
`atbwdt` INT(11) NULL DEFAULT NULL,
`atbwzt` INT(11) NULL DEFAULT NULL,
`atbart` VARCHAR(10) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
ALTER TABLE `w_findispmon05u`
CHANGE COLUMN `atpkey` `f5atpkey` INT(11) NOT NULL DEFAULT '0' FIRST,
CHANGE COLUMN `atzo05` `f5atzo05` INT(11) NULL DEFAULT NULL AFTER `f5atpkey`,
CHANGE COLUMN `atbwdt` `f5atbwdt` INT(11) NULL DEFAULT NULL AFTER `f5pBneu`,
CHANGE COLUMN `atbwzt` `f5atbwzt` INT(11) NULL DEFAULT NULL AFTER `f5atbwdt`,
CHANGE COLUMN `atbart` `f5atbart` VARCHAR(10) NULL DEFAULT NULL AFTER `f5atbwzt`,
ADD INDEX `atpkey` (`f5atpkey`),
ADD INDEX `inatkey` (`f5atzo05`, `pos`),
ADD INDEX `pos` (`pos`, `f5atzo05`);
SHOW WARNINGS;
Level	Code	Message
SHOW CREATE TABLE `w_findispmon05u`;
Table	Create Table
w_findispmon05u	CREATE TABLE `w_findispmon05u` (
  `f5atpkey` int(11) NOT NULL DEFAULT 0,
  `f5atzo05` int(11) DEFAULT NULL,
  `pos` bigint(21) DEFAULT NULL,
  `f5BnvB` int(9) DEFAULT NULL,
  `f5atbvb` int(11) DEFAULT NULL,
  `f5atbwmg` int(11) DEFAULT NULL,
  `f5pBneu` bigint(12) DEFAULT NULL,
  `f5atbwdt` int(11) DEFAULT NULL,
  `f5atbwzt` int(11) DEFAULT NULL,
  `f5atbart` varchar(10) DEFAULT NULL,
  KEY `atpkey` (`f5atpkey`),
  KEY `inatkey` (`f5atzo05`,`pos`),
  KEY `pos` (`pos`,`f5atzo05`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
DROP TABLE `w_findispmon05u`;
CREATE TABLE t (
a INT NOT NULL,
b INT NOT NULL,
PRIMARY KEY (a)
) ENGINE=INNODB;
ALTER TABLE t
CHANGE COLUMN b c INT NOT NULL,
ADD UNIQUE INDEX (c);
SHOW WARNINGS;
Level	Code	Message
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
ALTER TABLE t
CHANGE COLUMN c b INT NOT NULL,
ADD UNIQUE INDEX (c);
ERROR 42000: Key column 'c' doesn't exist in table
DROP TABLE t;
CREATE TABLE parent (
a INT NOT NULL,
b INT NOT NULL,
PRIMARY KEY (a)
) ENGINE=INNODB;
CREATE TABLE child (
a INT NOT NULL,
b INT NOT NULL,
PRIMARY KEY (a)
) ENGINE=INNODB;
ALTER TABLE child
CHANGE COLUMN b c INT NOT NULL,
ADD FOREIGN KEY (c) REFERENCES parent(a);
SHOW WARNINGS;
Level	Code	Message
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `a` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  KEY `c` (`c`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`c`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE child, parent;
CREATE TABLE IF NOT EXISTS ticket (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
mask VARCHAR(16) DEFAULT '' NOT NULL,
subject VARCHAR(255)  DEFAULT '' NOT NULL,
is_closed TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL,
is_deleted TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL,
team_id INT UNSIGNED DEFAULT 0 NOT NULL,
category_id INT UNSIGNED DEFAULT 0 NOT NULL,
first_message_id INT UNSIGNED DEFAULT 0 NOT NULL,
created_date INT UNSIGNED,
updated_date INT UNSIGNED,
due_date INT UNSIGNED,
first_wrote_address_id INT UNSIGNED NOT NULL DEFAULT 0,
last_wrote_address_id INT UNSIGNED NOT NULL DEFAULT 0,
spam_score DECIMAL(4,4) NOT NULL DEFAULT 0,
spam_training VARCHAR(1) NOT NULL DEFAULT '',
interesting_words VARCHAR(255) NOT NULL DEFAULT '',
next_action VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB;
ALTER TABLE ticket 
CHANGE COLUMN team_id group_id INT UNSIGNED NOT NULL DEFAULT 0,
CHANGE COLUMN category_id bucket_id INT UNSIGNED NOT NULL DEFAULT 0,
ADD COLUMN org_id INT UNSIGNED NOT NULL DEFAULT 0, 
ADD INDEX org_id (org_id);
SHOW CREATE TABLE ticket;
Table	Create Table
ticket	CREATE TABLE `ticket` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mask` varchar(16) NOT NULL DEFAULT '',
  `subject` varchar(255) NOT NULL DEFAULT '',
  `is_closed` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `is_deleted` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `group_id` int(10) unsigned NOT NULL DEFAULT 0,
  `bucket_id` int(10) unsigned NOT NULL DEFAULT 0,
  `first_message_id` int(10) unsigned NOT NULL DEFAULT 0,
  `created_date` int(10) unsigned DEFAULT NULL,
  `updated_date` int(10) unsigned DEFAULT NULL,
  `due_date` int(10) unsigned DEFAULT NULL,
  `first_wrote_address_id` int(10) unsigned NOT NULL DEFAULT 0,
  `last_wrote_address_id` int(10) unsigned NOT NULL DEFAULT 0,
  `spam_score` decimal(4,4) NOT NULL DEFAULT 0.0000,
  `spam_training` varchar(1) NOT NULL DEFAULT '',
  `interesting_words` varchar(255) NOT NULL DEFAULT '',
  `next_action` varchar(255) NOT NULL DEFAULT '',
  `org_id` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `org_id` (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE ticket;
CREATE TABLE t (
id bigint(20) unsigned NOT NULL auto_increment,
d date NOT NULL, 
a bigint(20) unsigned NOT NULL,
b smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (id,d)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs STATS_SAMPLE_PAGES=2
PARTITION BY RANGE COLUMNS(d)
(
PARTITION p20170914 VALUES LESS THAN ('2017-09-15') ENGINE = InnoDB,
PARTITION p99991231 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10);
insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10);
replace into t(d,a,b) select '2017-09-15',rand()*10000,rand()*10 from t t1, t t2, t t3, t t4;
select count(*) from t where d ='2017-09-15';
count(*)
18
ALTER TABLE t CHANGE b c smallint(5) unsigned , ADD KEY idx_d_a (d, a);
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `d` date NOT NULL,
  `a` bigint(20) unsigned NOT NULL,
  `c` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`,`d`),
  KEY `idx_d_a` (`d`,`a`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs STATS_SAMPLE_PAGES=2
 PARTITION BY RANGE  COLUMNS(`d`)
(PARTITION `p20170914` VALUES LESS THAN ('2017-09-15') ENGINE = InnoDB,
 PARTITION `p99991231` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)
analyze table t;
Table	Op	Msg_type	Msg_text
test.t	analyze	status	Engine-independent statistics collected
test.t	analyze	status	OK
select count(*) from t where d ='2017-09-15';
count(*)
18
select count(*) from t force index(primary) where d ='2017-09-15';
count(*)
18
DROP TABLE t;