ç®ä»
æ 论æ¯å¼å人åï¼è¿æ¯æ°æ®åºè¿ç»´äººåï¼ä¸å®è¦éµå®è§èï¼å »æ好çåSQLä¹ æ¯ï¼ä¼è®©ä½ å·¥ä½æ´å 顺é£é¡ºæ°´ï¼è®©ä½ ä¸åç»äººæåã
举ä¾è¡¨ç»æ
以ä¸ææçSQLæ¶åç表ç»æå¦ä¸æ示
mysql> show create table t_test_1G;*************************** 1. row *************************** Table: t_test_1Create Table: CREATE TABLE `t_test_1` ( `id` int(11) NOT NULL, `name` char(10) NOT NULL DEFAULT '', `name2` char(15) NOT NULL DEFAULT '', `status` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
ä¹ æ¯ä¸ï¼INSERTè¯å¥æ æ对åºçå段å称
å¨ä»£ç éï¼åinsertè¯å¥ï¼ä¸å®è¦åè¦æå ¥æ°æ®çå段å称ã
ä¸å¥½çä¹ æ¯ï¼ä¸åå段å称
insert into t_test_1 values(1,'name1','name2',1);
好çä¹ æ¯ï¼æç¡®åæè¦æå ¥æ°æ®çå段å称
insert into t_test_1(id,name,name2,status) values(1,'name1','name2',1);
å¯æ¯ä¸ºä»ä¹ä¸åå段å称ï¼æ¯ä¸å¥½çä¹ æ¯å¢ï¼å¨è¿é举ä¸ä¸ªå®é çæ¡ä¾ï¼åå¦æ天æä¸20ç¹åºç¨è¦ååæ´ä¸çº¿ï¼è¿æ¬¡ä¸çº¿ä¹åï¼éè¦å¨t_test_1表éæ·»å å段('pay_type')ï¼æ£å¸¸ä¸çº¿æµç¨ï¼è¯å®æ¯DBAå å表ç»æåæ´ï¼ç¶ååååºç¨ä¸çº¿ã好äºï¼ä¸é¢æ¥åä¸æ¬¡åæ´æä½
1.表(t_test_1)ç»æåæ´
mysql> alter table t_test_1 add pay_type smallint(1) not null default '0';Query OK, 0 rows affected (0.37 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table t_test_1G;*************************** 1. row *************************** Table: t_test_1Create Table: CREATE TABLE `t_test_1` ( `id` int(11) NOT NULL, `name` char(10) NOT NULL DEFAULT '', `name2` char(15) NOT NULL DEFAULT '', `status` int(11) NOT NULL DEFAULT '0', `pay_type` smallint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.01 sec)
2.表ç»ææ·»å 好å段ä¹åï¼åºç¨è¿æ²¡æä¸çº¿åï¼ä¼åçä»ä¹å¢ï¼
mysql> insert into t_test_1 values(2,'name2','name2',2);ERROR 1136 (21S01): Column count doesn't match value count at row 1
çå°äºå§ï¼ç±äºt_test_1表添å äºä¸ä¸ªæ°å段ï¼è¿ä¸ªæ¶åæ§è¡insertè¯å¥ï¼å°±ä¼æ¥éäºï¼è¿ä¸ªæ¶åï¼ä¼°è®¡ä¼ä¸å¤§å å°åè¦äºï¼ææ¥ä¹åï¼åªè½å é¤å段ï¼åæ»ä¸çº¿äºï¼èä¸å é¤å段ï¼ä¼å次导è´ä¸å¡å µå¡ï¼ä¸ä¸ªçº¿ä¸æ éå°±è¿ä¹åçäºï¼å为ä¸ä¸ªå¼å人åï¼ä½ è¿æ¢è¿ä¹åinsertè¯å¥åï¼
é¿å éå¼è½¬æ¢
åSQLè¯å¥æ¶ï¼ä¸å®è¦æ³¨æéå¼è½¬æ¢ï¼è¿ä¸ä» ä» ä¼å¯¼è´æ¥è¯¢æ§è½é®é¢ï¼èä¸ä¼å¯¼è´æ¥è¯¢ç»æä¸æ£ç¡®ã导è´æ¥è¯¢æ§è½é®é¢ï¼ä¼°è®¡å¤§é¨åæåææäºè§£ï¼å¯æ¯å¯¼è´æ¥è¯¢ç»æä¸æ£ç¡®ï¼ä¼°è®¡æä¸é¨åæåä¸å¤ªäºè§£ãä¸é¢å°±æ¥ä¸¾ä¾è¯´æä¸ä¸ã
1.æµè¯SQLè¯å¥
select * from t_test_1 where name2=1001
æ¥ççæµè¯SQLè¯å¥çæ§è¡è®¡å
mysql> explain select * from t_test_1 where name2=1001;+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t_test_1 | NULL | ALL | idx_t_test_1_name2 | NULL | NULL | NULL | 20 | 10.00 | Using where |+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+1 row in set, 3 warnings (0.02 sec)mysql> mysql> show warnings;+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Warning | 1739 | Cannot use ref access on index 'idx_t_test_1_name2' due to type or collation conversion on field 'name2' || Warning | 1739 | Cannot use range access on index 'idx_t_test_1_name2' due to type or collation conversion on field 'name2' || Note | 1003 | /* select#1 */ select `testdb`.`t_test_1`.`id` AS `id`,`testdb`.`t_test_1`.`name` AS `name`,`testdb`.`t_test_1`.`name2` AS `name2`,`testdb`.`t_test_1`.`status` AS `status` from `testdb`.`t_test_1` where (`testdb`.`t_test_1`.`name2` = 1001) |+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec)
çå°äºå§ï¼Cannot use range access on index 'idx_t_test_1_name2' due to type or collation conversion on field 'name2' ï¼å 为é士转æ¢ï¼å¯¼è´æ æ³ä½¿ç¨ç´¢å¼ãè¿ä¸ªSQLä¸ä» ä» åªæè¿ä¸ªé®é¢ï¼è¿æä¸ä¸ªæ´ä¸¥éçé®é¢ï¼æ¥è¯¢ç»æä¸æ£ç¡®ã
mysql> select * from t_test_1 where name2=1001;+----+--------+-------+--------+| id | name | name2 | status |+----+--------+-------+--------+| 1 | name1 | 1001 | 0 || 10 | name10 | 1001 | 0 || 11 | name11 | 1001 | 2 || 12 | name12 | 1001C | 3 |+----+--------+-------+--------+4 rows in set, 1 warning (0.01 sec)
å¨è¿éå¤æ¥è¯¢äºä¸æ¡è®°å½(id=12)ï¼æ以好çä¹ æ¯æ¯å¦æå段å®ä¹æå符串ï¼åæ¥è¯¢æ¡ä»¶ä¸å®è¦å¸¦åå¼å·
mysql> select * from t_test_1 where name2='1001';+----+--------+-------+--------+| id | name | name2 | status |+----+--------+-------+--------+| 1 | name1 | 1001 | 0 || 10 | name10 | 1001 | 0 || 11 | name11 | 1001 | 2 |+----+--------+-------+--------+3 rows in set (0.00 sec)
æ¯ä¸ªè¡¨å¿ é¡»è¦æ主é®
æ°æ®åºä¸æ¯å¼ 表ï¼å¿ é¡»è¦è®¾ç½®ä¸»é®ï¼è¿ä¸ªé常éè¦ï¼å¦æä¸è®¾ç½®ä¸»é®ï¼ä¼å¯¼è´è¡¨ä¸æ°æ®è®°å½éå¤ï¼å¯¼è´MySQLæ°æ®åºç主ä»å¤å¶å»¶è¿ã
ç¦æ¢ç¨select *
ç¦æ¢ç¨select * ï¼è¿ä¸ªå¾å¤å¼å人åç¥æï¼é¿å ç¨select * å¯ä»¥èçèµæºãåå°ç½ç»å¼éãå¯æ¯è½èçå¤å°å¢ï¼æ²¡æä¸ä¸ªæ¦å¿µï¼è¿éæ¥ä¸¾ä¸ªæ端çä¾åã
å设æä¸å¼ 带æä¸ä¸ªblobå段ç表ï¼è¿ä¸ªblobå段åå¨äºå¤§çº¦500Mçæ°æ®ï¼å¦æå端ç¨å页å±ç¤ºï¼æ¯é¡µæ¾ç¤º20æ¡è®°å½ï¼ä½æ¯ä¸éè¦æ¾ç¤ºè¿ä¸ªblobå段信æ¯ï¼ä½æ¯å¼å人åç¨select * ï¼å¯¼è´åå端è¿å20æ¡å¸¦æblobå段信æ¯ï¼è¿20æ¡è®°å½æ»å¤§å°ï¼20*500M=10Gï¼çå°äºï¼è¿åäº10Gçæ°æ®ï¼è¿ä¸ªæ°æ®é常æå¯è½ç´æ¥å°å端åºç¨ç¨åºå°±ç»å¼å å溢åºã
å ³æ³¨
1.å¦ææ¨å欢è¿ç¯æç« ï¼è¯·ç¹èµ+转åã
2.å¦ææ¨ç¹å«å欢ï¼è¯·å å ³æ³¨ã