应用 | 同学，该学MyCat实际应用案例与MyCat读写分离了
Hi！我是小小，一个双鱼座的佛系程序猿，今日的blog将会写关于MyCat最后一点学习内容，读写分离，与MyCat实际应用案例和一个小小的例子。
MyCat 读写分离
MyCat 配置
<dataNode name="dn1" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" /> <dataNode name="dn3" dataHost="localhost1" database="db3" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100"> <heartbeat>show slave status</heartbeat> <writeHost host="hostM" url="192.168.25.134:3306" user="root" password="root"> <readHost host="hostS" url="192.168.25.166:3306" user="root" password="root" /> </writeHost> </dataHost>
(1) 设置 balance="1"与writeType="0"
-
balance=“0”, 所有读操作都发送到当前可用的writeHost上。 -
balance=“1”，所有读操作都随机的发送到readHost。3. balance=“2”，所有读操作都随机的在writeHost、readhost上分发
-
writeType=“0”, 所有写操作都发送到可用的writeHost上。 -
writeType=“1”，所有写操作都随机的发送到readHost。3. writeType=“2”，所有写操作都随机的在writeHost、readhost分上发。
“readHost是从属于writeHost的，即意味着它从那个writeHost获取同步数据，因此，当它所属的writeHost宕机了，则它也不会再参与到读写分离中来，即“不工作了”，这是因为此时，它的数据已经“不可靠”了。基于这个考虑，目前mycat 1.3和1.4版本中，若想支持MySQL一主一从的标准配置，并且在主节点宕机的情况下，从节点还能读取数据，则需要在Mycat里配置为两个writeHost并设置banlance=1。”
(2) 设置 switchType="2" 与slaveThreshold="100"
项目案例
案例图
schema.xml
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="db_store" checkSQLschema="false" sqlMaxLimit="100"> <table name="tstore" dataNode="dn_store" primaryKey="ID" /> </schema> <schema name="db_user" checkSQLschema="false" sqlMaxLimit="100"> <!-- global table is auto cloned to all defined data nodes ,so can join with any table whose sharding node is in the same data node joinKey ：外键 parentKey ：指向外键的主键（tuser表） --> <table name="tcode" primaryKey="ID" type="global" dataNode="dn_user1,dn_user2" /> <table name="tuser" primaryKey="ID" dataNode="dn_user$1-2" rule="mod_userId_long"> <childTable name="tuaddress" primaryKey="ID" joinKey="uid" parentKey="id" /> </table> </schema> <dataNode name="dn_store" dataHost="storeHost" database="db_store" /> <dataNode name="dn_user1" dataHost="userHost1" database="db_user" /> <dataNode name="dn_user2" dataHost="userHost2" database="db_user" /> <!--Node Server1 balance=1 M/S --> <dataHost name="storeHost" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="192.168.24.128:3306" user="root" password="root"> <!-- can have multi read hosts --> <readHost host="hostS1" url="192.168.24.129:3306" user="root" password="root" /> </writeHost> </dataHost> <!--Node Server2 --> <dataHost name="userHost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="user_host1" url="192.168.24.128:3306" user="root" password="root" > </writeHost> </dataHost> <!--Node Server3 --> <dataHost name="userHost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="user_host2" url="192.168.24.129:3306" user="root" password="root"> </writeHost> </dataHost> </mycat:schema>
rule.xml
<?xml version="1.0" encoding="UTF-8"?> <!-- - - Licensed under the Apache License, Version 2.0 (the "License"); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. --> <!DOCTYPE mycat:rule SYSTEM "rule.dtd"> <mycat:rule xmlns:mycat="http://io.mycat/"> <tableRule name="rule1"> <rule> <columns>id</columns> <algorithm>func1</algorithm> </rule> </tableRule> <tableRule name="rule2"> <rule> <columns>user_id</columns> <algorithm>func1</algorithm> </rule> </tableRule> <tableRule name="sharding-by-intfile"> <rule> <columns>sharding_id</columns> <algorithm>hash-int</algorithm> </rule> </tableRule> <tableRule name="auto-sharding-long"> <rule> <columns>id</columns> <algorithm>rang-long</algorithm> </rule> </tableRule> <tableRule name="mod_userId_long"> <rule> <columns>id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <tableRule name="sharding-by-murmur"> <rule> <columns>id</columns> <algorithm>murmur</algorithm> </rule> </tableRule> <tableRule name="crc32slot"> <rule> <columns>id</columns> <algorithm>crc32slot</algorithm> </rule> </tableRule> <tableRule name="sharding-by-month"> <rule> <columns>create_time</columns> <algorithm>partbymonth</algorithm> </rule> </tableRule> <tableRule name="latest-month-calldate"> <rule> <columns>calldate</columns> <algorithm>latestMonth</algorithm> </rule> </tableRule> <tableRule name="auto-sharding-rang-mod"> <rule> <columns>id</columns> <algorithm>rang-mod</algorithm> </rule> </tableRule> <tableRule name="jch"> <rule> <columns>id</columns> <algorithm>jump-consistent-hash</algorithm> </rule> </tableRule> <tableRule name="mod_userId_long"> <rule> <columns>id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash"> <property name="seed">0</property><!-- 默认是0 --> <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则 没法分片 --> <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点 被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 --> <!-- <property name="weightMapFile">weightMapFile</property> 节点的权重， 没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索 引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 --> <!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的 murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 -- > </function> <function name="crc32slot" class="io.mycat.route.function.PartitionByCRC32PreSlot"> <property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则 没法分片 --> </function> <function name="hash-int" class="io.mycat.route.function.PartitionByFileMap"> <property name="mapFile">partition-hash-int.txt</property> </function> <function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong"> <property name="mapFile">autopartition-long.txt</property> </function> <function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!-- how many data nodes --> <property name="count">2</property> </function> <function name="func1" class="io.mycat.route.function.PartitionByLong"> <property name="partitionCount">8</property> <property name="partitionLength">128</property> </function> <function name="latestMonth" class="io.mycat.route.function.LatestMonthPartion"> <property name="splitOneDay">24</property> </function> <function name="partbymonth" class="io.mycat.route.function.PartitionByMonth"> <property name="dateFormat">yyyy-MM-dd</property> <property name="sBeginDate">2015-01-01</property> </function> <function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod"> <property name="mapFile">partition-range-mod.txt</property> </function> <function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash"> <property name="totalBuckets">3</property> </function> </mycat:rule>
server.xml
<?xml version="1.0" encoding="UTF-8"?> <!-- - - Licensed under the Apache License, Version 2.0 (the "License"); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. --> <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 --> <property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检 测、0为关闭 --> <property name="sequnceHandlerType">2</property> <!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协 议--> <!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟 的MySQL版本号--> <!-- <property name="processorBufferChunk">40960</property> --> <!-- <property name="processors">1</property> 进程数 <property name="processorExecutor">32</property> 线程数 --> <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena--> <property name="processorBufferPoolType">0</property> <!--默认是65535 64K 用于sql解析时最大文本长度 --> <!--<property name="maxStringLiteralLength">65535</property>--> <!--<property name="sequnceHandlerType">0</property>--> <!--<property name="backSocketNoDelay">1</property>--> <!--<property name="frontSocketNoDelay">1</property>--> <!--<property name="processorExecutor">16</property>--> <!-- <property name="serverPort">8066</property> <property name="managerPort">9066</property> <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> --> <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局 表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志--> <property name="handleDistributedTransactions">0</property> <!-- off heap for merge/order/group/limit 1开启 0关闭 --> <property name="useOffHeapForMerge">1</property> <!-- 单位为m --> <property name="memoryPageSize">1m</property> <!-- 单位为k --> <property name="spillsFileBufferSize">1k</property> <property name="useStreamOutput">0</property> <!-- 单位为m --> <property name="systemReserveMemorySize">384m</property> <!--是否采用zookeeper协调切换 --> <property name="useZKSwitch">true</property> </system> <!-- 全局SQL防火墙设置 --> <!-- <firewall> <whitehost> 白名单 <host host="127.0.0.1" user="mycat"/> <host host="127.0.0.2" user="mycat"/> </whitehost> 黑名单 <blacklist check="false"> </blacklist> </firewall> --> <user name="root"> <property name="password">123456</property> <property name="schemas">db_store,db_user</property> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> IUSD （1：可操作0：不可操作） <schema name="TESTDB" dml="0110" > <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> --> </user> </mycat:server>
步骤：
实例
用户购物下单实践
需求
业务表
CREATE TABLE tb_user ( login_name VARCHAR (32) comment '登录名', user_id BIGINT comment '用户标识', TYPE INT comment '用户类型 1 商家，2买家', passwd VARCHAR (128) comment '密码', primary key (user_id) ) ; alter table tb_user comment '用户表'; CREATE TABLE tb_seller_order ( seller_user_id BIGINT comment '商家标识', buyer_user_id BIGINT comment '用户标识', order_id BIGINT comment '订单标识', price BIGINT comment '价格', STATUS INT comment '状态', primary key (order_id) ); alter table tb_seller_order comment '商家订单表'; CREATE TABLE tb_order_detail( seller_user_id BIGINT comment '商家标识', order_detail_id BIGINT comment '订单明细标识', order_id BIGINT comment '订单标识', goods_id BIGINT comment '标识', goods_name VARCHAR(32) comment '商品名称', cnt INT comment '数量', unit_price INT comment '单价', primary key (order_detail_id) ); alter table tb_order_detail comment '订单明细'; CREATE TABLE tb_buyer_order ( buyer_user_id BIGINT comment '用户标识', seller_user_id BIGINT comment '商家标识', order_id BIGINT comment '订单标识', price BIGINT comment '价格', STATUS INT comment '状态', primary key (order_id) ); alter table tb_buyer_order comment '买家订单';
服务器分配
MySql
主 192.168.10.135 主 192.168.10.136 主 192.168.10.137
mycat：
192.168.10.137
表主键生成策略
添加MyCat全局序列号
USER_ID_SQUE=dn_master ORDER_ID_SQUE=dn_master DETAIL_ID_SQUE=dn_master
在dn_master结点执行：
insert into MYCAT_SEQUENCE(NAME,current_value,increment) values('USER_ID_SQUE',1,100),('ORDER_ID_SQUE',1,100),('DETAIL_ID_SQUE',1,100);
重启mycat测试：
SELECT NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE; SELECT NEXT VALUE FOR MYCATSEQ_ORDER_ID_SQUE; SELECT NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE;
rule.xml配置
配置function 标签
<function name="mod-long" class="org.opencloudb.route.function.PartitionByMod"> <!-- how many data nodes --> <property name="count">3</property> </function>
配置tableRule标签
<!-- user分片规则 --> <tableRule name="tb_user_mod-long"> <rule> <!-- 分片使用的字段 --> <columns>user_id</columns> <!-- 分片使用的方法，对应function 名称 --> <algorithm>mod-long</algorithm> </rule> </tableRule> <!-- 卖家订单分片规则 --> <tableRule name="seller_order_mod-long"> <rule> <columns>seller_user_id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <!-- 买家订单分片规则 --> <tableRule name="buyer_order_mod-long"> <rule> <columns>buyer_user_id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule>
schema.xml 配置
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://org.opencloudb/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> <!-- auto sharding by id (long) --> <table name="tb_a" dataNode="dn_master" /> <!-- 全局表，使用type属性指定，多个结点要都执行建表语句。所有结点的数据一致。--> <table name="tb_global_test" dataNode="dn_master,dn_master2,dn_master3" type="global" /> <!-- 配置表所在的分片结点，指定主键和分片规则。指定主键是为了使用主键查询时mycat什么 缓存主键对应的dn，提高查询效率。--> <table name="tb_user" rule="tb_user_mod-long" primaryKey="user_id" dataNode="dn_master,dn_master2,dn_master3" /> <table name="tb_seller_order" rule="seller_order_mod-long" primaryKey="order_id" dataNode="dn_master,dn_master2,dn_master3"> <!-- 配置ER 分片，子表的存储依赖于主表，并且物理上紧邻存放。--> <childTable name="tb_order_detail" primaryKey="order_detail_id" joinKey="order_id" parentKey="order_id" /> </table> <table name="tb_buyer_order" rule="buyer_order_mod-long" primaryKey="order_id" dataNode="dn_master,dn_master2,dn_master3" /> </schema> <!-- 配置数据结点 --> <dataNode name="dn_master" dataHost="master" database="test_db" /> <dataNode name="dn_master2" dataHost="master2" database="test_db" /> <dataNode name="dn_master3" dataHost="master3" database="test_db" /> <!-- ddata node host 配置 --> <dataHost name="master" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <!-- 主从心跳语句配置 --> <heartbeat>show slave status</heartbeat> <writeHost host="hostM1" url="192.168.109.128:3306" user="root" password="Abc123!@#"> <!-- 从库 --> <readHost host="hostS2" url="192.168.109.130:3306" user="root" password="Abc123!@#" /> </writeHost> </dataHost> <dataHost name="master2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM2" url="192.168.109.131:3306" user="root" password="Abc123!@#"></writeHost> </dataHost> <dataHost name="master3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM3" url="192.168.109.132:3306" user="root" password="Abc123!@#"></writeHost> </dataHost> </mycat:schema>
测试
插入user
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-1',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-2',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-3',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-4',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-5',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-6',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-7',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-8',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-9',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-10',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-11',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-12',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-13',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-14',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-15',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-16',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-17',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-18',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-19',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-20',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-21',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-22',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('name-23',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,1,'passwd-A');
下单测试
INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('buyer-1',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,2,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('buyer-2',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,2,'passwd-A'); INSERT INTO `tb_user`(`login_name`,`user_id`,`TYPE`,`passwd`) VALUES ('buyer-3',NEXT VALUE FOR MYCATSEQ_USER_ID_SQUE,2,'passwd-A');
248 | 2 | passwd-A | | buyer-2 | 249 | 2 | passwd-A | | buyer-3 | 250 | 2 | passwdA | | name-1 | 225 | 1 | passwd-A | | name-10 | 234 | 1 | passwd-A | | name-11 | 235 | 1 | passwd-A | | name-12 | 236 | 1 | passwd-A | | name-13 | 237 | 1 |passwd-A | | name-14 | 238 | 1 | passwd-A | | name-15 | 239 | 1 | passwd-A | |name-16 | 240 | 1 | passwd-A | | name-17 | 241 | 1 | passwd-A | | name-18 |
242 | 1 | passwd-A | | name-19 | 243 | 1 | passwd-A | | name-2 | 226 | 1 |passwd-A | | name-20 | 244 | 1 | passwd-A | | name-21 | 245 | 1 | passwd-A | |name-22 | 246 | 1 | passwd-A | | name-23 | 247 | 1 | passwd-A | | name-3 |
227 | 1 | passwd-A | | name-4 | 228 | 1 | passwd-A | | name-5 | 229 | 1 |passwd-A | | name-6 | 230 | 1 | passwd-A | | name-7 | 231 | 1 | passwd-A | |name-8 | 232 | 1 | passwd-A | | name-9 | 233 | 1 | passwd-A | +------------+---------+------+----------+
下单
SELECT NEXT VALUE FOR MYCATSEQ_ORDER_ID_SQUE; INSERT INTO `tb_seller_order`(`seller_user_id`,`buyer_user_id`,`order_id`,`price`) VALUES (225,248,201,1222); INSERT INTO `tb_order_detail` (`seller_user_id`,`order_detail_id`,`order_id`,`goods_id`,`goods_name`,`cnt`,`un it_price`) VALUES (225, NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE,201,11,'goods_name',1,1220); INSERT INTO `tb_order_detail` (`seller_user_id`,`order_detail_id`,`order_id`,`goods_id`,`goods_name`,`cnt`,`un it_price`) VALUES (225, NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE,201,11,'goods_name2',1,2);
INSERT INTO `tb_seller_order`(`seller_user_id`,`buyer_user_id`,`order_id`,`price`) VALUES (238,248,203,1222); INSERT INTO `tb_order_detail` (`seller_user_id`,`order_detail_id`,`order_id`,`goods_id`,`goods_name`,`cnt`,`un it_price`) VALUES (238, NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE,203,11,'goods_name',1,1220); INSERT INTO `tb_order_detail` (`seller_user_id`,`order_detail_id`,`order_id`,`goods_id`,`goods_name`,`cnt`,`un it_price`) VALUES (238, NEXT VALUE FOR MYCATSEQ_DETAIL_ID_SQUE,203,11,'goods_name2',1,2);
1 | passwd-A | | name-4 | 228 | 1 | passwd-A | | name-7 | 231 | 1 | passwd-A | |name-10 | 234 | 1 | passwd-A | | name-13 | 237 | 1 | passwd-A | | name-16 |
240 | 1 | passwd-A | | name-19 | 243 | 1 | passwd-A | | name-22 | 246 | 1 |passwd-A | | buyer-2 | 249 | 2 | passwd-A | +------------+---------+------+----------+ 9 rows in set(0.00 sec)mysql> select * from tb_seller_order; +----------------+---------------+----------+-------+--------+ |seller_user_id | buyer_user_id | order_id | price | STATUS | +----------------+---------------+----------+-------+--------+ | 225 | 248 | 201 | 1222 | NULL | +----------------+---------------+----------+-------+--------+ 1 row in set (0.00 sec)mysql> select * from tb_order_detail; +----------------+-----------------+----------+----------+-------------+------+------------+ | seller_user_id | order_detail_id | order_id | goods_id | goods_name | cnt | unit_price| +----------------+-----------------+----------+----------+-------------+------+------------+ | 225 | 201 |
201 | 11 | goods_name | 1 | 1220 | | 225 | 202 | 201 | 11 |goods_name2 | 1 | 2 | +----------------+-----------------+----------+----------+-------------+------+------------
-
2 rows in set (0.00 sec)109.131数据为：mysql> select * from tb_user; +------------+---------+------+----------+ | login_name |user_id | TYPE | passwd | +------------+---------+------+----------+ | name-2 | 226 | 1 | passwd-A| | name-5 | 229 | 1 | passwd-A | | name-8 | 232 | 1 | passwd-A | | name-11 |
235 | 1 | passwd-A | | name-14 | 238 | 1 | passwd-A | | name-17 | 241 | 1 |passwd-A | | name-20 | 244 | 1 | passwd-A | | name-23 | 247 | 1 | passwd-A | |buyer-3 | 250 | 2 | passwd-A | +------------+---------+------+----------+ 9 rows in set (0.00 sec)mysql> select * from tb_seller_order; +----------------+---------------+----------+-------+--------+ |seller_user_id | buyer_user_id | order_id | price | STATUS | +----------------+---------------+----------+-------+--------+ | 238 | 248 | 203 | 1222 | NULL | +----------------+---------------+----------+-------+--------+ 1 row in set (0.00 sec)mysql> select * from tb_order_detail; +----------------+-----------------+----------+----------+-------------+------+------------+ | seller_user_id | order_detail_id | order_id | goods_id | goods_name | cnt | unit_price| +----------------+-----------------+----------+----------+-------------+------+------------+ | 238 | 203 |
203 | 11 | goods_name | 1 | 1220 | | 238 | 204 | 203 | 11 |goods_name2 | 1 | 2 | +----------------+-----------------+----------+----------+-------------+------+------------ -
2 rows in set (0.00 sec)
关于作者
小明菜市场
●
●
●
●
●
给我个好看再走好吗？