type_varchar.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

drop table if exists t1, t2;
create table t1 (v varchar(30), c char(3), e enum('abc','def','ghi'), t text);
truncate table vchar;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `v` varchar(30) default NULL,
  `c` char(3) default NULL,
  `e` enum('abc','def','ghi') default NULL,
  `t` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table vchar;
Table	Create Table
vchar	CREATE TABLE `vchar` (
  `v` varchar(30) default NULL,
  `c` char(3) default NULL,
  `e` enum('abc','def','ghi') default NULL,
  `t` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('abc', 'de', 'ghi', 'jkl');
insert into t1 values ('abc ', 'de ', 'ghi', 'jkl ');
insert into t1 values ('abc    ', 'd  ', 'ghi', 'jkl    ');
insert into vchar values ('abc', 'de', 'ghi', 'jkl');
insert into vchar values ('abc ', 'de ', 'ghi', 'jkl ');
insert into vchar values ('abc    ', 'd  ', 'ghi', 'jkl    ');
select length(v),length(c),length(e),length(t) from t1;
length(v)	length(c)	length(e)	length(t)
3	2	3	3
4	2	3	4
7	1	3	7
select length(v),length(c),length(e),length(t) from vchar;
length(v)	length(c)	length(e)	length(t)
3	2	3	3
3	2	3	4
3	1	3	7
alter table vchar add i int;
show create table vchar;
Table	Create Table
vchar	CREATE TABLE `vchar` (
  `v` varchar(30) default NULL,
  `c` char(3) default NULL,
  `e` enum('abc','def','ghi') default NULL,
  `t` text,
  `i` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select length(v),length(c),length(e),length(t) from vchar;
length(v)	length(c)	length(e)	length(t)
3	2	3	3
3	2	3	4
3	1	3	7
drop table t1, vchar;
create table t1 (v varchar(20));
insert into t1 values('a ');
select v='a' from t1;
v='a'
1
select binary v='a' from t1;
binary v='a'
0
select binary v='a ' from t1;
binary v='a '
1
insert into t1 values('a');
alter table t1 add primary key (v);
ERROR 23000: Duplicate entry 'a' for key 1
drop table t1;
create table t1 (v varbinary(20));
insert into t1 values('a');
insert into t1 values('a ');
alter table t1 add primary key (v);
drop table t1;
create table t1 (v varchar(254), index (v));
insert into t1 values ("This is a test ");
insert into t1 values ("Some sample data");
insert into t1 values (" garbage ");
insert into t1 values (" This is a test ");
insert into t1 values ("This is a test");
insert into t1 values ("Hello world");
insert into t1 values ("Foo bar");
insert into t1 values ("This is a test");
insert into t1 values ("MySQL varchar test");
insert into t1 values ("test MySQL varchar");
insert into t1 values ("This is a long string to have some random length data included");
insert into t1 values ("Short string");
insert into t1 values ("VSS");
insert into t1 values ("Some samples");
insert into t1 values ("Bar foo");
insert into t1 values ("Bye");
select * from t1 where v like 'This is a test' order by v;
v
This is a test
This is a test
select * from t1 where v='This is a test' order by v;
v
This is a test 
This is a test
This is a test
select * from t1 where v like 'S%' order by v;
v
Short string
Some sample data
Some samples
explain select * from t1 where v like 'This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	257	NULL	3	Using where; Using index
explain select * from t1 where v='This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	257	const	3	Using where; Using index
explain select * from t1 where v like 'S%' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	257	NULL	2	Using where; Using index
alter table t1 change v v varchar(255);
select * from t1 where v like 'This is a test' order by v;
v
This is a test
This is a test
select * from t1 where v='This is a test' order by v;
v
This is a test 
This is a test
This is a test
select * from t1 where v like 'S%' order by v;
v
Short string
Some sample data
Some samples
explain select * from t1 where v like 'This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	258	NULL	3	Using where; Using index
explain select * from t1 where v='This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	258	const	3	Using where; Using index
explain select * from t1 where v like 'S%' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	258	NULL	2	Using where; Using index
alter table t1 change v v varchar(256);
select * from t1 where v like 'This is a test' order by v;
v
This is a test
This is a test
select * from t1 where v='This is a test' order by v;
v
This is a test 
This is a test
This is a test
select * from t1 where v like 'S%' order by v;
v
Short string
Some sample data
Some samples
explain select * from t1 where v like 'This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	259	NULL	3	Using where; Using index
explain select * from t1 where v='This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	259	const	3	Using where; Using index
explain select * from t1 where v like 'S%' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	259	NULL	2	Using where; Using index
alter table t1 change v v varchar(257);
select * from t1 where v like 'This is a test' order by v;
v
This is a test
This is a test
select * from t1 where v='This is a test' order by v;
v
This is a test 
This is a test
This is a test
select * from t1 where v like 'S%' order by v;
v
Short string
Some sample data
Some samples
explain select * from t1 where v like 'This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	260	NULL	3	Using where; Using index
explain select * from t1 where v='This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	260	const	3	Using where; Using index
explain select * from t1 where v like 'S%' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	260	NULL	2	Using where; Using index
alter table t1 change v v varchar(258);
select * from t1 where v like 'This is a test' order by v;
v
This is a test
This is a test
select * from t1 where v='This is a test' order by v;
v
This is a test 
This is a test
This is a test
select * from t1 where v like 'S%' order by v;
v
Short string
Some sample data
Some samples
explain select * from t1 where v like 'This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	261	NULL	3	Using where; Using index
explain select * from t1 where v='This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	261	const	3	Using where; Using index
explain select * from t1 where v like 'S%' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	261	NULL	2	Using where; Using index
alter table t1 change v v varchar(259);
select * from t1 where v like 'This is a test' order by v;
v
This is a test
This is a test
select * from t1 where v='This is a test' order by v;
v
This is a test 
This is a test
This is a test
select * from t1 where v like 'S%' order by v;
v
Short string
Some sample data
Some samples
explain select * from t1 where v like 'This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	262	NULL	3	Using where; Using index
explain select * from t1 where v='This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	262	const	3	Using where; Using index
explain select * from t1 where v like 'S%' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	262	NULL	2	Using where; Using index
alter table t1 change v v varchar(258);
select * from t1 where v like 'This is a test' order by v;
v
This is a test
This is a test
select * from t1 where v='This is a test' order by v;
v
This is a test 
This is a test
This is a test
select * from t1 where v like 'S%' order by v;
v
Short string
Some sample data
Some samples
explain select * from t1 where v like 'This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	261	NULL	3	Using where; Using index
explain select * from t1 where v='This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	261	const	3	Using where; Using index
explain select * from t1 where v like 'S%' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	261	NULL	2	Using where; Using index
alter table t1 change v v varchar(257);
select * from t1 where v like 'This is a test' order by v;
v
This is a test
This is a test
select * from t1 where v='This is a test' order by v;
v
This is a test 
This is a test
This is a test
select * from t1 where v like 'S%' order by v;
v
Short string
Some sample data
Some samples
explain select * from t1 where v like 'This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	260	NULL	3	Using where; Using index
explain select * from t1 where v='This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	260	const	3	Using where; Using index
explain select * from t1 where v like 'S%' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	260	NULL	2	Using where; Using index
alter table t1 change v v varchar(256);
select * from t1 where v like 'This is a test' order by v;
v
This is a test
This is a test
select * from t1 where v='This is a test' order by v;
v
This is a test 
This is a test
This is a test
select * from t1 where v like 'S%' order by v;
v
Short string
Some sample data
Some samples
explain select * from t1 where v like 'This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	259	NULL	3	Using where; Using index
explain select * from t1 where v='This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	259	const	3	Using where; Using index
explain select * from t1 where v like 'S%' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	259	NULL	2	Using where; Using index
alter table t1 change v v varchar(255);
select * from t1 where v like 'This is a test' order by v;
v
This is a test
This is a test
select * from t1 where v='This is a test' order by v;
v
This is a test 
This is a test
This is a test
select * from t1 where v like 'S%' order by v;
v
Short string
Some sample data
Some samples
explain select * from t1 where v like 'This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	258	NULL	3	Using where; Using index
explain select * from t1 where v='This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	258	const	3	Using where; Using index
explain select * from t1 where v like 'S%' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	258	NULL	2	Using where; Using index
alter table t1 change v v varchar(254);
select * from t1 where v like 'This is a test' order by v;
v
This is a test
This is a test
select * from t1 where v='This is a test' order by v;
v
This is a test 
This is a test
This is a test
select * from t1 where v like 'S%' order by v;
v
Short string
Some sample data
Some samples
explain select * from t1 where v like 'This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	257	NULL	3	Using where; Using index
explain select * from t1 where v='This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	257	const	3	Using where; Using index
explain select * from t1 where v like 'S%' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	257	NULL	2	Using where; Using index
alter table t1 change v v varchar(253);
alter table t1 change v v varchar(254), drop key v;
alter table t1 change v v varchar(300), add key (v(10));
select * from t1 where v like 'This is a test' order by v;
v
This is a test
This is a test
select * from t1 where v='This is a test' order by v;
v
This is a test 
This is a test
This is a test
select * from t1 where v like 'S%' order by v;
v
Short string
Some sample data
Some samples
explain select * from t1 where v like 'This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	13	NULL	4	Using where; Using filesort
explain select * from t1 where v='This is a test' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	13	const	4	Using where
explain select * from t1 where v like 'S%' order by v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	13	NULL	2	Using where; Using filesort
drop table t1;
create table t1 (pkcol varchar(16), othercol varchar(16), primary key (pkcol));
insert into t1 values ('test', 'something');
update t1 set othercol='somethingelse' where pkcol='test';
select * from t1;
pkcol	othercol
test	somethingelse
drop table t1;
create table t1 (a int, b varchar(12));
insert into t1 values (1, 'A'), (22, NULL);
create table t2 (a int);
insert into t2 values (22), (22);
select t1.a, t1.b, min(t1.b) from t1 inner join t2 ON t2.a = t1.a 
group by t1.b, t1.a;
a	b	min(t1.b)
22	NULL	NULL
drop table t1, t2;
create table t1 (f1 varchar(65500));
create index index1 on t1(f1(10));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` varchar(65500) default NULL,
  KEY `index1` (`f1`(10))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
alter table t1 modify f1 varchar(255);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` varchar(255) default NULL,
  KEY `index1` (`f1`(10))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
alter table t1 modify f1 tinytext;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` tinytext,
  KEY `index1` (`f1`(10))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(f1 VARCHAR(100) DEFAULT 'test');
INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3));
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(f1 CHAR(100) DEFAULT 'test');
INSERT INTO t1 VALUES(SUBSTR(f1, 1, 3));
DROP TABLE IF EXISTS t1;