type_blob.result
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:16k
- drop table if exists t1,t2,t3,t4,t5,t6,t7;
- CREATE TABLE t1 (a blob, b text, c blob(250), d text(70000), e text(70000000));
- show columns from t1;
- Field Type Null Key Default Extra
- a blob YES NULL
- b text YES NULL
- c tinyblob YES NULL
- d mediumtext YES NULL
- e longtext YES NULL
- CREATE TABLE t2 (a char(257), b varbinary(70000), c varchar(70000000));
- Warnings:
- Warning 1246 Converting column 'a' from CHAR to TEXT
- Warning 1246 Converting column 'b' from CHAR to BLOB
- Warning 1246 Converting column 'c' from CHAR to TEXT
- show columns from t2;
- Field Type Null Key Default Extra
- a text YES NULL
- b mediumblob YES NULL
- c longtext YES NULL
- create table t3 (a long, b long byte);
- show create TABLE t3;
- Table Create Table
- t3 CREATE TABLE `t3` (
- `a` mediumtext,
- `b` mediumblob
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1
- drop table t1,t2,t3
- #;
- CREATE TABLE t1 (a char(257) default "hello");
- ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
- CREATE TABLE t2 (a blob default "hello");
- ERROR 42000: BLOB/TEXT column 'a' can't have a default value
- drop table if exists t1,t2;
- create table t1 (nr int(5) not null auto_increment,b blob,str char(10), primary key (nr));
- insert into t1 values (null,"a","A");
- insert into t1 values (null,"bbb","BBB");
- insert into t1 values (null,"ccc","CCC");
- select last_insert_id();
- last_insert_id()
- 3
- select * from t1,t1 as t2;
- nr b str nr b str
- 1 a A 1 a A
- 2 bbb BBB 1 a A
- 3 ccc CCC 1 a A
- 1 a A 2 bbb BBB
- 2 bbb BBB 2 bbb BBB
- 3 ccc CCC 2 bbb BBB
- 1 a A 3 ccc CCC
- 2 bbb BBB 3 ccc CCC
- 3 ccc CCC 3 ccc CCC
- drop table t1;
- create table t1 (a text);
- insert into t1 values ('where');
- update t1 set a='Where';
- select * from t1;
- a
- Where
- drop table t1;
- create table t1 (t text,c char(10),b blob, d binary(10));
- insert into t1 values (NULL,NULL,NULL,NULL);
- insert into t1 values ("","","","");
- insert into t1 values ("hello","hello","hello","hello");
- insert into t1 values ("HELLO","HELLO","HELLO","HELLO");
- insert into t1 values ("HELLO MY","HELLO MY","HELLO MY","HELLO MY");
- insert into t1 values ("a","a","a","a");
- insert into t1 values (1,1,1,1);
- insert into t1 values (NULL,NULL,NULL,NULL);
- update t1 set c="",b=null where c="1";
- lock tables t1 READ;
- show full fields from t1;
- Field Type Collation Null Key Default Extra Privileges Comment
- t text latin1_swedish_ci YES NULL #
- c varchar(10) latin1_swedish_ci YES NULL #
- b blob NULL YES NULL #
- d varbinary(10) NULL YES NULL #
- lock tables t1 WRITE;
- show full fields from t1;
- Field Type Collation Null Key Default Extra Privileges Comment
- t text latin1_swedish_ci YES NULL #
- c varchar(10) latin1_swedish_ci YES NULL #
- b blob NULL YES NULL #
- d varbinary(10) NULL YES NULL #
- unlock tables;
- select t from t1 where t like "hello";
- t
- hello
- HELLO
- select c from t1 where c like "hello";
- c
- hello
- HELLO
- select b from t1 where b like "hello";
- b
- hello
- select d from t1 where d like "hello";
- d
- hello
- select c from t1 having c like "hello";
- c
- hello
- HELLO
- select d from t1 having d like "hello";
- d
- hello
- select t from t1 where t like "%HELLO%";
- t
- hello
- HELLO
- HELLO MY
- select c from t1 where c like "%HELLO%";
- c
- hello
- HELLO
- HELLO MY
- select b from t1 where b like "%HELLO%";
- b
- HELLO
- HELLO MY
- select d from t1 where d like "%HELLO%";
- d
- HELLO
- HELLO MY
- select c from t1 having c like "%HELLO%";
- c
- hello
- HELLO
- HELLO MY
- select d from t1 having d like "%HELLO%";
- d
- HELLO
- HELLO MY
- select d from t1 having d like "%HE%LLO%";
- d
- HELLO
- HELLO MY
- select t from t1 order by t;
- t
- NULL
- NULL
- 1
- a
- hello
- HELLO
- HELLO MY
- select c from t1 order by c;
- c
- NULL
- NULL
- a
- hello
- HELLO
- HELLO MY
- select b from t1 order by b;
- b
- NULL
- NULL
- NULL
- HELLO
- HELLO MY
- a
- hello
- select d from t1 order by d;
- d
- NULL
- NULL
- 1
- HELLO
- HELLO MY
- a
- hello
- select distinct t from t1;
- t
- NULL
- hello
- HELLO MY
- a
- 1
- select distinct b from t1;
- b
- NULL
- hello
- HELLO
- HELLO MY
- a
- select distinct t from t1 order by t;
- t
- NULL
- 1
- a
- hello
- HELLO MY
- select distinct b from t1 order by b;
- b
- NULL
- HELLO
- HELLO MY
- a
- hello
- select t from t1 group by t;
- t
- NULL
- 1
- a
- hello
- HELLO MY
- select b from t1 group by b;
- b
- NULL
- HELLO
- HELLO MY
- a
- hello
- set option sql_big_tables=1;
- select distinct t from t1;
- t
- NULL
- hello
- HELLO MY
- a
- 1
- select distinct b from t1;
- b
- NULL
- hello
- HELLO
- HELLO MY
- a
- select distinct t from t1 order by t;
- t
- NULL
- 1
- a
- hello
- HELLO MY
- select distinct b from t1 order by b;
- b
- NULL
- HELLO
- HELLO MY
- a
- hello
- select distinct c from t1;
- c
- NULL
- hello
- HELLO MY
- a
- select distinct d from t1;
- d
- NULL
- hello
- HELLO
- HELLO MY
- a
- 1
- select distinct c from t1 order by c;
- c
- NULL
- a
- hello
- HELLO MY
- select distinct d from t1 order by d;
- d
- NULL
- 1
- HELLO
- HELLO MY
- a
- hello
- select c from t1 group by c;
- c
- NULL
- a
- hello
- HELLO MY
- select d from t1 group by d;
- d
- NULL
- 1
- HELLO
- HELLO MY
- a
- hello
- set option sql_big_tables=0;
- select distinct * from t1;
- t c b d
- NULL NULL NULL NULL
-
- hello hello hello hello
- HELLO HELLO HELLO HELLO
- HELLO MY HELLO MY HELLO MY HELLO MY
- a a a a
- 1 NULL 1
- select t,count(*) from t1 group by t;
- t count(*)
- NULL 2
- 1
- 1 1
- a 1
- hello 2
- HELLO MY 1
- select b,count(*) from t1 group by b;
- b count(*)
- NULL 3
- 1
- HELLO 1
- HELLO MY 1
- a 1
- hello 1
- select c,count(*) from t1 group by c;
- c count(*)
- NULL 2
- 2
- a 1
- hello 2
- HELLO MY 1
- select d,count(*) from t1 group by d;
- d count(*)
- NULL 2
- 1
- 1 1
- HELLO 1
- HELLO MY 1
- a 1
- hello 1
- drop table t1;
- create table t1 (a text, unique (a(2100)));
- ERROR 42000: Specified key was too long; max key length is 1000 bytes
- create table t1 (a text, key (a(2100)));
- Warnings:
- Warning 1071 Specified key was too long; max key length is 1000 bytes
- show create table t1;
- Table Create Table
- t1 CREATE TABLE `t1` (
- `a` text,
- KEY `a` (`a`(1000))
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1
- drop table t1;
- CREATE TABLE t1 (
- t1_id bigint(21) NOT NULL auto_increment,
- _field_72 varchar(128) DEFAULT '' NOT NULL,
- _field_95 varchar(32),
- _field_115 tinyint(4) DEFAULT '0' NOT NULL,
- _field_122 tinyint(4) DEFAULT '0' NOT NULL,
- _field_126 tinyint(4),
- _field_134 tinyint(4),
- PRIMARY KEY (t1_id),
- UNIQUE _field_72 (_field_72),
- KEY _field_115 (_field_115),
- KEY _field_122 (_field_122)
- );
- INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
- INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
- INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
- CREATE TABLE t2 (
- seq_0_id bigint(21) DEFAULT '0' NOT NULL,
- seq_1_id bigint(21) DEFAULT '0' NOT NULL,
- PRIMARY KEY (seq_0_id,seq_1_id)
- );
- INSERT INTO t2 VALUES (1,1);
- INSERT INTO t2 VALUES (2,1);
- INSERT INTO t2 VALUES (2,2);
- CREATE TABLE t3 (
- t3_id bigint(21) NOT NULL auto_increment,
- _field_131 varchar(128),
- _field_133 tinyint(4) DEFAULT '0' NOT NULL,
- _field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
- _field_137 tinyint(4),
- _field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
- _field_140 blob,
- _field_142 tinyint(4) DEFAULT '0' NOT NULL,
- _field_145 tinyint(4) DEFAULT '0' NOT NULL,
- _field_148 tinyint(4) DEFAULT '0' NOT NULL,
- PRIMARY KEY (t3_id),
- KEY _field_133 (_field_133),
- KEY _field_135 (_field_135),
- KEY _field_139 (_field_139),
- KEY _field_142 (_field_142),
- KEY _field_145 (_field_145),
- KEY _field_148 (_field_148)
- );
- INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','testrnjobrn1',0,0,0);
- INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);
- CREATE TABLE t4 (
- seq_0_id bigint(21) DEFAULT '0' NOT NULL,
- seq_1_id bigint(21) DEFAULT '0' NOT NULL,
- PRIMARY KEY (seq_0_id,seq_1_id)
- );
- INSERT INTO t4 VALUES (1,1);
- INSERT INTO t4 VALUES (2,1);
- CREATE TABLE t5 (
- t5_id bigint(21) NOT NULL auto_increment,
- _field_149 tinyint(4),
- _field_156 varchar(128) DEFAULT '' NOT NULL,
- _field_157 varchar(128) DEFAULT '' NOT NULL,
- _field_158 varchar(128) DEFAULT '' NOT NULL,
- _field_159 varchar(128) DEFAULT '' NOT NULL,
- _field_160 varchar(128) DEFAULT '' NOT NULL,
- _field_161 varchar(128) DEFAULT '' NOT NULL,
- PRIMARY KEY (t5_id),
- KEY _field_156 (_field_156),
- KEY _field_157 (_field_157),
- KEY _field_158 (_field_158),
- KEY _field_159 (_field_159),
- KEY _field_160 (_field_160),
- KEY _field_161 (_field_161)
- );
- INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
- INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
- CREATE TABLE t6 (
- seq_0_id bigint(21) DEFAULT '0' NOT NULL,
- seq_1_id bigint(21) DEFAULT '0' NOT NULL,
- PRIMARY KEY (seq_0_id,seq_1_id)
- );
- INSERT INTO t6 VALUES (1,1);
- INSERT INTO t6 VALUES (1,2);
- INSERT INTO t6 VALUES (2,2);
- CREATE TABLE t7 (
- t7_id bigint(21) NOT NULL auto_increment,
- _field_143 tinyint(4),
- _field_165 varchar(32),
- _field_166 smallint(6) DEFAULT '0' NOT NULL,
- PRIMARY KEY (t7_id),
- KEY _field_166 (_field_166)
- );
- INSERT INTO t7 VALUES (1,0,'High',1);
- INSERT INTO t7 VALUES (2,0,'Medium',2);
- INSERT INTO t7 VALUES (3,0,'Low',3);
- select replace(t3._field_140, "r","^M"),t3_id,min(t3._field_131), min(t3._field_135), min(t3._field_139), min(t3._field_137), min(link_alias_142._field_165), min(link_alias_133._field_72), min(t3._field_145), min(link_alias_148._field_156), replace(min(t3._field_140), "r","^M"),t3.t3_id from t3 left join t4 on t4.seq_0_id = t3.t3_id left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id left join t6 on t6.seq_0_id = t3.t3_id left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id left join t2 on t2.seq_0_id = t3.t3_id left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id where t3.t3_id in (1) group by t3.t3_id order by link_alias_142._field_166, _field_139, link_alias_133._field_72, _field_135, link_alias_148._field_156;
- replace(t3._field_140, "r","^M") t3_id min(t3._field_131) min(t3._field_135) min(t3._field_139) min(t3._field_137) min(link_alias_142._field_165) min(link_alias_133._field_72) min(t3._field_145) min(link_alias_148._field_156) replace(min(t3._field_140), "r","^M") t3_id
- test^M
- job^M
- 1 1 test job 1 0000-00-00 00:00:00 1999-02-25 22:43:32 0 High admin 0 tomato test^M
- job^M
- 1 1
- drop table t1,t2,t3,t4,t5,t6,t7;
- create table t1 (a blob);
- insert into t1 values ("empty"),("");
- select a,reverse(a) from t1;
- a reverse(a)
- empty ytpme
-
- drop table t1;
- create table t1 (a blob, key (a(10)));
- insert into t1 values ("bye"),("hello"),("hello"),("hello word");
- select * from t1 where a like "hello%";
- a
- hello
- hello
- hello word
- drop table t1;
- CREATE TABLE t1 (
- f1 int(11) DEFAULT '0' NOT NULL,
- f2 varchar(16) DEFAULT '' NOT NULL,
- f5 text,
- KEY index_name (f1,f2,f5(16))
- );
- INSERT INTO t1 VALUES (0,'traktor','1111111111111');
- INSERT INTO t1 VALUES (1,'traktor','1111111111111111111111111');
- select count(*) from t1 where f2='traktor';
- count(*)
- 2
- drop table t1;
- create table t1 (foobar tinyblob not null, boggle smallint not null, key (foobar(32), boggle));
- insert into t1 values ('fish', 10),('bear', 20);
- select foobar, boggle from t1 where foobar = 'fish';
- foobar boggle
- fish 10
- select foobar, boggle from t1 where foobar = 'fish' and boggle = 10;
- foobar boggle
- fish 10
- drop table t1;
- create table t1 (id integer auto_increment unique,imagem LONGBLOB not null);
- insert into t1 (id) values (1);
- select
- charset(load_file('../../std_data/words.dat')),
- collation(load_file('../../std_data/words.dat')),
- coercibility(load_file('../../std_data/words.dat'));
- charset(load_file('../../std_data/words.dat')) collation(load_file('../../std_data/words.dat')) coercibility(load_file('../../std_data/words.dat'))
- binary binary 4
- explain extended select
- charset(load_file('../../std_data/words.dat')),
- collation(load_file('../../std_data/words.dat')),
- coercibility(load_file('../../std_data/words.dat'));
- id select_type table type possible_keys key key_len ref rows Extra
- 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
- Warnings:
- Note 1003 select sql_no_cache charset(load_file(_latin1'../../std_data/words.dat')) AS `charset(load_file('../../std_data/words.dat'))`,collation(load_file(_latin1'../../std_data/words.dat')) AS `collation(load_file('../../std_data/words.dat'))`,coercibility(load_file(_latin1'../../std_data/words.dat')) AS `coercibility(load_file('../../std_data/words.dat'))`
- update t1 set imagem=load_file('../../std_data/words.dat') where id=1;
- select if(imagem is null, "ERROR", "OK"),length(imagem) from t1 where id = 1;
- if(imagem is null, "ERROR", "OK") length(imagem)
- OK 581
- drop table t1;
- create table t1 select load_file('../../std_data/words.dat') l;
- show full fields from t1;
- Field Type Collation Null Key Default Extra Privileges Comment
- l longblob NULL YES NULL #
- drop table t1;
- create table t1 (id integer primary key auto_increment, txt text not null, unique index txt_index (txt (20)));
- insert into t1 (txt) values ('Chevy'), ('Chevy ');
- select * from t1 where txt='Chevy';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt='Chevy ';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt='Chevy ' or txt='Chevy';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt='Chevy' or txt='Chevy ';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where id='1' or id='2';
- id txt
- 1 Chevy
- 2 Chevy
- insert into t1 (txt) values('Ford');
- select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';
- id txt
- 1 Chevy
- 2 Chevy
- 3 Ford
- select * from t1 where txt='Chevy' or txt='Chevy ';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt in ('Chevy ','Chevy');
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt in ('Chevy');
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt between 'Chevy' and 'Chevy';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt between 'Chevy' and 'Chevy ';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt < 'Chevy ';
- id txt
- select * from t1 where txt <= 'Chevy';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt > 'Chevy';
- id txt
- 3 Ford
- select * from t1 where txt >= 'Chevy';
- id txt
- 1 Chevy
- 2 Chevy
- 3 Ford
- drop table t1;
- create table t1 (id integer primary key auto_increment, txt text, unique index txt_index (txt (20)));
- insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL);
- select * from t1 where txt='Chevy' or txt is NULL;
- id txt
- 3 NULL
- 1 Chevy
- 2 Chevy
- explain select * from t1 where txt='Chevy' or txt is NULL;
- id select_type table type possible_keys key key_len ref rows Extra
- 1 SIMPLE t1 range txt_index txt_index 23 NULL 2 Using where
- select * from t1 where txt='Chevy ';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt='Chevy ' or txt='Chevy';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt='Chevy' or txt='Chevy ';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where id='1' or id='2';
- id txt
- 1 Chevy
- 2 Chevy
- insert into t1 (txt) values('Ford');
- select * from t1 where txt='Chevy' or txt='Chevy ' or txt='Ford';
- id txt
- 1 Chevy
- 2 Chevy
- 4 Ford
- select * from t1 where txt='Chevy' or txt='Chevy ';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt='Chevy' or txt='Chevy ' or txt=' Chevy';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt in ('Chevy ','Chevy');
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt in ('Chevy');
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt between 'Chevy' and 'Chevy';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt between 'Chevy' and 'Chevy' or txt='Chevy ';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt between 'Chevy' and 'Chevy ';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt < 'Chevy ';
- id txt
- select * from t1 where txt < 'Chevy ' or txt is NULL;
- id txt
- 3 NULL
- select * from t1 where txt <= 'Chevy';
- id txt
- 1 Chevy
- 2 Chevy
- select * from t1 where txt > 'Chevy';
- id txt
- 4 Ford
- select * from t1 where txt >= 'Chevy';
- id txt
- 1 Chevy
- 2 Chevy
- 4 Ford
- alter table t1 modify column txt blob;
- explain select * from t1 where txt='Chevy' or txt is NULL;
- id select_type table type possible_keys key key_len ref rows Extra
- 1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where
- select * from t1 where txt='Chevy' or txt is NULL;
- id txt
- 1 Chevy
- 3 NULL
- explain select * from t1 where txt='Chevy' or txt is NULL order by txt;
- id select_type table type possible_keys key key_len ref rows Extra
- 1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where; Using filesort
- select * from t1 where txt='Chevy' or txt is NULL order by txt;
- id txt
- 3 NULL
- 1 Chevy
- drop table t1;
- CREATE TABLE t1 ( i int(11) NOT NULL default '0', c text NOT NULL, d varchar(1) NOT NULL DEFAULT ' ', PRIMARY KEY (i), KEY (c(1),d));
- INSERT t1 (i, c) VALUES (1,''),(2,''),(3,'asdfh'),(4,'');
- select max(i) from t1 where c = '';
- max(i)
- 4
- drop table t1;