mysql版本15.1没有分区表

xxe27gdn 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(304)

我正在尝试创建一个表，该表将使用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

mysql partitioning phpmyadmin

来源：https://stackoverflow.com/questions/52613295/mysql-ver-15-1-doesnt-partition-table

1条答案

按热度按时间

63lcw9qa1#

几乎没有理由使用“分区选择”。您应该让引擎计算出要查找哪个（或多个）分区以满足查询。
现在，你应该放弃myisam，搬到innodb。 int(3) 是一个4字节的数字。这个 (3) 什么都没有。仰望 TINYINT .
此表上没有索引？使用索引来提高性能，而不是分区。
如果你有 INDEX(fechaconsulta) ，这将获取相同的行：

SELECT * FROM 
    WHERE fechaconsulta >= '2009-01-01'
      AND fechaconsulta  < '2009-01-01' + INTERVAL 1 YEAR;

不管有没有分区，它都能有效地完成。

赞(0）回复(0）举报 2021-06-21

我来回答

mysql版本15.1没有分区表

1条答案

相关问题

热门标签

最新问答