<pre name="code" class="html">mysql> desc ProductInfo;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| sn | int(11) | NO | PRI | NULL | |
| productIntro | text | NO | | NULL | |
| borrowerInfo | text | NO | | NULL | |
| realBorrower | varchar(128) | YES | | NULL | |
| capitalPurpose | text | NO | | NULL | |
| repaySource | text | NO | | NULL | |
| riskInfo | text | NO | | NULL | |
| safeguard | varchar(50) | NO | | | |
| contractSn | int(11) | YES | | NULL | |
| delegator | int(11) | NO | | NULL | |
| custody | varchar(125) | NO | | | |
| riskLevel | char(1) | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
mysql> show index from ProductIno;
ERROR 1146 (42S02): Table 'zjzc.ProductIno' doesn't exist
mysql> show index from ProductInfo;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ProductInfo | 0 | PRIMARY | 1 | sn | A | 283 | NULL | NULL | | BTREE | | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
mysql> select sn,contractSn from ProductInfo where contractSn=45;
+-----+------------+
| sn | contractSn |
+-----+------------+
| 58 | 45 |
| 301 | 45 |
+-----+------------+
2 rows in set (0.00 sec)
mysql> explain update ProductInfo set contractSn=99 where contractSn=45;
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | ProductInfo | index | NULL | PRIMARY | 4 | NULL | 283 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.07 sec)
mysql> create index ProductInfo_idx1 on ProductInfo(contractSn);
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain update ProductInfo set contractSn=99 where contractSn=45;
+----+-------------+-------------+-------+------------------+------------------+---------+-------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+------------------+------------------+---------+-------+------+------------------------------+
| 1 | SIMPLE | ProductInfo | range | ProductInfo_idx1 | ProductInfo_idx1 | 5 | const | 2 | Using where; Using temporary |
+----+-------------+-------------+-------+------------------+------------------+---------+-------+------+------------------------------+
1 row in set (0.00 sec)
/***************************************************************************************************8
mysql> show create table test100G;
*************************** 1. row ***************************
Table: test100
Create Table: CREATE TABLE `test100` (
`sn` int(11) NOT NULL COMMENT 'product sn',
`productIntro` text NOT NULL COMMENT '产品介绍',
`borrowerInfo` text NOT NULL COMMENT '借款方信息',
`realBorrower` varchar(128) DEFAULT NULL COMMENT '实际借款人',
`capitalPurpose` text NOT NULL COMMENT '资金用途',
`repaySource` text NOT NULL COMMENT '还款来源',
`riskInfo` text NOT NULL COMMENT '风控信息',
`safeguard` varchar(50) NOT NULL DEFAULT '' COMMENT '保障信息代码列表，格式1,2,3,4',
`contractSn` int(11) DEFAULT NULL COMMENT '合同模版',
`delegator` int(11) NOT NULL COMMENT '产品委托人',
`custody` varchar(125) NOT NULL DEFAULT '' COMMENT '产品管理人',
`riskLevel` char(1) NOT NULL COMMENT '风险级别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show index from test100;
Empty set (0.00 sec)
mysql> explain update test100 set contractSn=99 where contractSn=45;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test100 | ALL | NULL | NULL | NULL | NULL | 283 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> create index test100_idx1 on test100(contractSn);
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain update test100 set contractSn=99 where contractSn=45;
+----+-------------+---------+-------+---------------+--------------+---------+-------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+--------------+---------+-------+------+------------------------------+
| 1 | SIMPLE | test100 | range | test100_idx1 | test100_idx1 | 5 | const | 2 | Using where; Using temporary |
+----+-------------+---------+-------+---------------+--------------+---------+-------+------+------------------------------+
1 row in set (0.03 sec)