文档中心 > 简单创建插入语句
简单创建插入语句

最近更新时间:2023-04-10

使用vitess首先需要创建keyspace(database),然后创建vschema vindexes,最后建表开始使用。

  1. 控制台创建keyspace,可创建多Shard Keyspace和单Shard Keyspace。

  2. 通过MySQL客户端连接Vtgate查看Keyspace列表。

  3. cloudos at vitess-c8t2pr2j53ae_vtgate1_node0 in ~
    $ mysql -hXXXXXX -utest -P15000 -p

    mysql>  show vitess_keyspaces;

    +-------------------------+

    | Database                |

    +-------------------------+

    | db1                     |

    | ruan                    |

    | default_ks_locuk0f8edo0 |

    +-------------------------+

    3 rows in set (0.00 sec)

  4. 查看Shard列表

  5. mysql> show vitess_shards;

    +-----------------------------+

    | Shards                      |

    +-----------------------------+

    | db1/0                       |

    | default_ks_locuk0f8edo0/-80 |

    | default_ks_locuk0f8edo0/80- |

    | ruan/-80                    |

    | ruan/80-                    |

    +-----------------------------+

    5 rows in set (0.01 sec)

  6. 查看Tablets 列表

  7. mysql> show vitess_tablets;

    +-------+-------------------------+-------+------------+---------+------------------+--------------+----------------------+

    | Cell  | Keyspace                | Shard | TabletType | State   | Alias            | Hostname     | PrimaryTermStartTime |

    +-------+-------------------------+-------+------------+---------+------------------+--------------+----------------------+

    | cell1 | db1                     | 0     | PRIMARY    | SERVING | cell1-0302876118 | 10.128.0.239 | 2022-03-04T01:46:20Z |

    | cell1 | db1                     | 0     | REPLICA    | SERVING | cell1-4025292932 | 10.128.0.243 |                      |

    | cell1 | db1                     | 0     | REPLICA    | SERVING | cell1-1381239232 | 10.128.0.244 |                      |

    | cell1 | default_ks_locuk0f8edo0 | -80   | PRIMARY    | SERVING | cell1-4171879776 | 10.128.0.237 | 2022-03-03T08:17:52Z |

    | cell1 | default_ks_locuk0f8edo0 | -80   | REPLICA    | SERVING | cell1-1977247623 | 10.128.0.238 |                      |

    | cell1 | default_ks_locuk0f8edo0 | -80   | REPLICA    | SERVING | cell1-1779214502 | 10.128.0.240 |                      |

    | cell1 | default_ks_locuk0f8edo0 | 80-   | PRIMARY    | SERVING | cell1-2661903991 | 10.128.0.239 | 2022-03-03T08:17:52Z |

    | cell1 | default_ks_locuk0f8edo0 | 80-   | REPLICA    | SERVING | cell1-3522658939 | 10.128.0.244 |                      |

    | cell1 | default_ks_locuk0f8edo0 | 80-   | REPLICA    | SERVING | cell1-2056157307 | 10.128.0.243 |                      |

    | cell1 | ruan                    | -80   | PRIMARY    | SERVING | cell1-2450360630 | 10.128.0.237 | 2022-03-04T02:07:21Z |

    | cell1 | ruan                    | -80   | REPLICA    | SERVING | cell1-3722567318 | 10.128.0.240 |                      |

    | cell1 | ruan                    | -80   | REPLICA    | SERVING | cell1-2618405723 | 10.128.0.238 |                      |

    | cell1 | ruan                    | 80-   | PRIMARY    | SERVING | cell1-0181255056 | 10.128.0.239 | 2022-03-04T02:07:21Z |

    | cell1 | ruan                    | 80-   | REPLICA    | SERVING | cell1-0624333780 | 10.128.0.243 |                      |

    | cell1 | ruan                    | 80-   | REPLICA    | SERVING | cell1-2000206826 | 10.128.0.244 |                      |

    +-------+-------------------------+-------+------------+---------+------------------+--------------+----------------------+

    15 rows in set (0.00 sec)

  8.  选择keyspace, 建vschema, 建表,插入数据

  9. mysql> use ruan;

    Database changed
    mysql> show vschema tables;
    +--------+
    | Tables |
    +--------+
    | dual   |
    +--------+
    1 row in set (0.00 sec)

    mysql> show vschema vindexes;
    Empty set (0.00 sec)

    ****多shard必须先创建vschema,否则将会触发以下报错。

    mysql> create table product(
        ->   sku varbinary(128),
        ->   description varbinary(128),
        ->   price bigint,
        ->   primary key(sku)
        -> ) ENGINE=InnoDB;
    ERROR 1105 (HY000): Keyspace does not have exactly one shard: [0xc000b487f8 0xc000b48810]

    更改(创建)多分片数据分发方式。

    这里填写第一

    mysql> alter vschema on multi.customer add vindex hash(customer_id) using hash;
    Query OK, 0 rows affected (0.00 sec)

    mysql> show vschema tables;
    +----------+
    | Tables   |
    +----------+
    | customer |
    | dual     |
    +----------+
    2 rows in set (0.00 sec)

    mysql> show vschema vindexes;
    +----------+------+------+--------+-------+
    | Keyspace | Name | Type | Params | Owner |
    +----------+------+------+--------+-------+
    | multi    | hash | hash |        |       |
    +----------+------+------+--------+-------+
    1 row in set (0.01 sec)

    mysql> create table customer(customer_id bigint, uname varchar(128), primary key(customer_id));
    Query OK, 0 rows affected (0.05 sec)

    mysql> show tables;

    +----------------+

    | Tables_in_ruan |

    +----------------+

    | customer       |

    +----------------+

    1 row in set (0.01 sec)

     

     

    mysql>  insert into customer (customer_id,uname) values ( 1, 'wenlong' );

    Query OK, 1 row affected (0.02 sec)

     

    mysql>  select * from customer;

    +-------------+---------+

    | customer_id | uname   |

    +-------------+---------+

    |           1 | wenlong |

    +-------------+---------+

    1 row in set (0.00 sec)


     查看数据落到了哪个分片上:

    mysql> use `ruan:80-`;

    Reading table information for completion of table and column names

    You can turn off this feature to get a quicker startup with -A

     

    Database changed

    mysql> select * from customer;

    Empty set (0.00 sec)

     

    mysql> use `ruan:-80`;

    Reading table information for completion of table and column names

    You can turn off this feature to get a quicker startup with -A

     

    Database changed

    mysql> select * from customer;

    +-------------+---------+

    | customer_id | uname   |

    +-------------+---------+

    |           1 | wenlong |

    +-------------+---------+

    1 row in set (0.01 sec)