我正在尝试创建一个表,该表将使用phpmyadmin在具有以下内容的服务器中按年范围进行分区:
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
(使用 mysql -V
在centos 7)
我用来创建的代码是:
CREATE TABLE `datosenfermedades` (
`clues` varchar(15) NOT NULL,
`unidadmedica` varchar(75) NOT NULL,
`fechaconsulta` datetime NOT NULL,
`idconsulta` int(15) NOT NULL,
`clavediagnostico` varchar(6) NOT NULL,
`primeravez` tinyint(1) NOT NULL,
`clavemunicipio` int(3) NOT NULL,
`edad` int(3) NOT NULL,
`sexo` varchar(1) NOT NULL,
`grupoedad` varchar(20) NOT NULL,
`derechohabiencia` varchar(30) NOT NULL,
`servicio` varchar(40) NOT NULL,
`nivelatencion` int(1) NOT NULL
)
PARTITION BY RANGE (year(fechaconsulta)) (
PARTITION p0 VALUES LESS THAN (2008) ENGINE=MyISAM,
PARTITION p1 VALUES LESS THAN (2009) ENGINE=MyISAM,
PARTITION p2 VALUES LESS THAN (2010) ENGINE=MyISAM,
PARTITION p3 VALUES LESS THAN (2011) ENGINE=MyISAM,
PARTITION p4 VALUES LESS THAN (2012) ENGINE=MyISAM,
PARTITION p5 VALUES LESS THAN (2013) ENGINE=MyISAM,
PARTITION p6 VALUES LESS THAN (2014) ENGINE=MyISAM,
PARTITION p7 VALUES LESS THAN (2015) ENGINE=MyISAM,
PARTITION p8 VALUES LESS THAN (2016) ENGINE=MyISAM,
PARTITION p9 VALUES LESS THAN (2017) ENGINE=MyISAM,
PARTITION p10 VALUES LESS THAN (2018) ENGINE=MyISAM,
PARTITION p11 VALUES LESS THAN (2019) ENGINE=MyISAM,
PARTITION p12 VALUES LESS THAN (2020) ENGINE=MyISAM,
PARTITION p13 VALUES LESS THAN (2021) ENGINE=MyISAM,
PARTITION p14 VALUES LESS THAN (2022) ENGINE=MyISAM,
PARTITION p15 VALUES LESS THAN (2023) ENGINE=MyISAM,
PARTITION p16 VALUES LESS THAN (2024) ENGINE=MyISAM,
PARTITION p17 VALUES LESS THAN (2025) ENGINE=MyISAM,
PARTITION p18 VALUES LESS THAN (2026) ENGINE=MyISAM,
PARTITION p19 VALUES LESS THAN (2027) ENGINE=MyISAM,
PARTITION p20 VALUES LESS THAN (2028) ENGINE=MyISAM,
PARTITION p21 VALUES LESS THAN (2029) ENGINE=MyISAM,
PARTITION p22 VALUES LESS THAN (2030) ENGINE=MyISAM,
PARTITION p23 VALUES LESS THAN (2031) ENGINE=MyISAM,
PARTITION p24 VALUES LESS THAN (2032) ENGINE=MyISAM,
PARTITION p25 VALUES LESS THAN (2033) ENGINE=MyISAM,
PARTITION p26 VALUES LESS THAN (2034) ENGINE=MyISAM,
PARTITION p27 VALUES LESS THAN (2035) ENGINE=MyISAM,
PARTITION p28 VALUES LESS THAN (2036) ENGINE=MyISAM,
PARTITION p29 VALUES LESS THAN (2037) ENGINE=MyISAM,
PARTITION p30 VALUES LESS THAN (2038) ENGINE=MyISAM,
PARTITION p31 VALUES LESS THAN (2039) ENGINE=MyISAM,
PARTITION p32 VALUES LESS THAN (2040) ENGINE=MyISAM,
PARTITION p33 VALUES LESS THAN (2041) ENGINE=MyISAM,
PARTITION p34 VALUES LESS THAN (2042) ENGINE=MyISAM,
PARTITION p35 VALUES LESS THAN (2043) ENGINE=MyISAM,
PARTITION p36 VALUES LESS THAN (2044) ENGINE=MyISAM,
PARTITION p37 VALUES LESS THAN (2045) ENGINE=MyISAM,
PARTITION p38 VALUES LESS THAN (2046) ENGINE=MyISAM,
PARTITION p39 VALUES LESS THAN (2047) ENGINE=MyISAM,
PARTITION p40 VALUES LESS THAN (2048) ENGINE=MyISAM,
PARTITION p41 VALUES LESS THAN (2049) ENGINE=MyISAM,
PARTITION p42 VALUES LESS THAN MAXVALUE ENGINE=MyISAM
);
表显然是正确创建的,当我使用突击队 SHOW CREATE TABLE datosenfermedades
我得到:
CREATE TABLE `datosenfermedades` (
`clues` varchar(15) COLLATE utf8_spanish_ci NOT NULL,
`unidadmedica` varchar(75) COLLATE utf8_spanish_ci NOT NULL,
`fechaconsulta` datetime NOT NULL,
`idconsulta` int(15) NOT NULL,
`clavediagnostico` varchar(6) COLLATE utf8_spanish_ci NOT NULL,
`primeravez` tinyint(1) NOT NULL,
`clavemunicipio` int(3) NOT NULL,
`edad` int(3) NOT NULL,
`sexo` varchar(1) COLLATE utf8_spanish_ci NOT NULL,
`grupoedad` varchar(20) COLLATE utf8_spanish_ci NOT NULL,
`derechohabiencia` varchar(30) COLLATE utf8_spanish_ci NOT NULL,
`servicio` varchar(40) COLLATE utf8_spanish_ci NOT NULL,
`nivelatencion` int(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci
/*!50100 PARTITION BY RANGE (year(fechaconsulta))
(PARTITION p0 VALUES LESS THAN (2008) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2009) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (2010) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2011) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (2012) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (2013) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (2014) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (2015) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (2016) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (2017) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (2018) ENGINE = MyISAM,
PARTITION p11 VALUES LESS THAN (2019) ENGINE = MyISAM,
PARTITION p12 VALUES LESS THAN (2020) ENGINE = MyISAM,
PARTITION p13 VALUES LESS THAN (2021) ENGINE = MyISAM,
PARTITION p14 VALUES LESS THAN (2022) ENGINE = MyISAM,
PARTITION p15 VALUES LESS THAN (2023) ENGINE = MyISAM,
PARTITION p16 VALUES LESS THAN (2024) ENGINE = MyISAM,
PARTITION p17 VALUES LESS THAN (2025) ENGINE = MyISAM,
PARTITION p18 VALUES LESS THAN (2026) ENGINE = MyISAM,
PARTITION p19 VALUES LESS THAN (2027) ENGINE = MyISAM,
PARTITION p20 VALUES LESS THAN (2028) ENGINE = MyISAM,
PARTITION p21 VALUES LESS THAN (2029) ENGINE = MyISAM,
PARTITION p22 VALUES LESS THAN (2030) ENGINE = MyISAM,
PARTITION p23 VALUES LESS THAN (2031) ENGINE = MyISAM,
PARTITION p24 VALUES LESS THAN (2032) ENGINE = MyISAM,
PARTITION p25 VALUES LESS THAN (2033) ENGINE = MyISAM,
PARTITION p26 VALUES LESS THAN (2034) ENGINE = MyISAM,
PARTITION p27 VALUES LESS THAN (2035) ENGINE = MyISAM,
PARTITION p28 VALUES LESS THAN (2036) ENGINE = MyISAM,
PARTITION p29 VALUES LESS THAN (2037) ENGINE = MyISAM,
PARTITION p30 VALUES LESS THAN (2038) ENGINE = MyISAM,
PARTITION p31 VALUES LESS THAN (2039) ENGINE = MyISAM,
PARTITION p32 VALUES LESS THAN (2040) ENGINE = MyISAM,
PARTITION p33 VALUES LESS THAN (2041) ENGINE = MyISAM,
PARTITION p34 VALUES LESS THAN (2042) ENGINE = MyISAM,
PARTITION p35 VALUES LESS THAN (2043) ENGINE = MyISAM,
PARTITION p36 VALUES LESS THAN (2044) ENGINE = MyISAM,
PARTITION p37 VALUES LESS THAN (2045) ENGINE = MyISAM,
PARTITION p38 VALUES LESS THAN (2046) ENGINE = MyISAM,
PARTITION p39 VALUES LESS THAN (2047) ENGINE = MyISAM,
PARTITION p40 VALUES LESS THAN (2048) ENGINE = MyISAM,
PARTITION p41 VALUES LESS THAN (2049) ENGINE = MyISAM,
PARTITION p42 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
如果我是正确的mysql只是评论我的分区指令。毕竟,从2007年到2017年,当我尝试使用突击队时,我在表格中插入了3142157个值。” SELECT * FROM datosenfermedades PARTITION (p1)
“我得到下一个错误:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(p1)
LIMIT 0, 25' at line 1
1条答案
按热度按时间42fyovps1#
几乎没有理由使用“分区选择”。您应该让引擎计算出要查找哪个(或多个)分区以满足查询。
现在,你应该放弃myisam,搬到innodb。
int(3)
是一个4字节的数字。这个(3)
什么都没有。仰望TINYINT
.此表上没有索引?使用索引来提高性能,而不是分区。
如果你有
INDEX(fechaconsulta)
,这将获取相同的行:不管有没有分区,它都能有效地完成。