回复:Re:_Re:Re:_auto-inc_column_as_secondary_field_to_compose_a_primary_key_in_mysql_cluster

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

回复:Re:_Re:Re:_auto-inc_column_as_secondary_field_to_compose_a_primary_key_in_mysql_cluster

JohnXhark
Thank you Jesper, it clarifies a lot.

JX


----- 原始邮件 -----
发件人:Jesper Wisborg Krogh <[hidden email]>
收件人:[hidden email]
主题:Re:_Re:Re:_auto-inc_column_as_secondary_field_to_compose_a_primary_key_in_mysql_cluster
日期:2016年11月17日 16点14分

Hi John,
On 17/11/2016 16:40, JohnXhark wrote:
> Hi Mauritz,
> Thanks for your answering.
> I found this from the doc:
> "...an InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column."
>
> So my index definition will not work for innoDB, and it will not work as expected for MyISAM as you just said. it seems this is a mess.
Actually I've seen requests for InnoDB to behave like MyISAM, so guess
whether MyISAM behaves as expected depends on the use case. Anyway
that's a side question.
> But, specifically, will it work as expected for NDB engine?
Yes you can do that for NDB:
    mysql> CREATE TABLE sales (
              customer_id int unsigned NOT NULL,
              sales_id int unsigned NOT NULL auto_increment,
              description varchar(40) NOT NULL, value decimal(6,2) NOT NULL,
              PRIMARY KEY (customer_id, sales_id)
            ) ENGINE=ndbcluster PARTITION BY KEY (customer_id) PARTITIONS 8;
    Query OK, 0 rows affected (0.36 sec)
    mysql> INSERT INTO sales (customer_id, description, value) VALUES
    (1, 'towel', 2.55);
    Query OK, 1 row affected (0.02 sec)
    mysql> INSERT INTO sales (customer_id, description, value) VALUES
    (1, 'pencil', 1.43);
    Query OK, 1 row affected (0.00 sec)
    mysql> INSERT INTO sales (customer_id, description, value) VALUES
    (2, 'laptop', 1299.99);
    Query OK, 1 row affected (0.01 sec)
    mysql> INSERT INTO sales (customer_id, description, value) VALUES
    (1, 'knife', 5.99);
    Query OK, 1 row affected (0.00 sec)
    mysql> SELECT * FROM sales ORDER BY customer_id, sales_id;
    +-------------+----------+-------------+---------+
    | customer_id | sales_id | description | value   |
    +-------------+----------+-------------+---------+
    |           1 |        1 | towel       |    2.55 |
    |           1 |        2 | pencil      |    1.43 |
    |           1 |        4 | knife       |    5.99 |
    |           2 |        3 | laptop      | 1299.99 |
    +-------------+----------+-------------+---------+
    4 rows in set (0.01 sec)
Note however there is no requirement from a Cluster perspective for the
partition key to be the first column in the Primary Key. So the
following table would work the same:
    CREATE TABLE sales (
       sales_id int unsigned NOT NULL auto_increment,
       customer_id int unsigned NOT NULL,
       description varchar(40) NOT NULL,
       value decimal(6,2) NOT NULL,
       PRIMARY KEY (sales_id, customer_id)
    ) ENGINE=ndbcluster PARTITION BY KEY (customer_id) PARTITIONS 8;
Does that help?
Best regards,
Jesper Krogh
MySQL Support
Loading...