mysql> insert into wison_test1 set id=1,name='wison';-------相对于后一条insert语句,返回的提示信息少了一行
Query OK, 1 row affected (0.08 sec)mysql> select * from wison_test1;
+------+-------+| id | name |+------+-------+| 1 | wison |+------+-------+1 row in set (0.00 sec)mysql> insert into wison_test1 select 2,'Wison';
Query OK, 1 row affected (0.10 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from wison_test1;
+------+-------+| id | name |+------+-------+| 1 | wison || 2 | Wison |+------+-------+2 rows in set (0.00 sec)2.数据类型带长度mysql> create table wison_test2(id int(5));Query OK, 0 rows affected (0.12 sec)mysql> insert into wison_test2 select 1;
Query OK, 1 row affected (0.10 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from wison_test2;
+------+| id |+------+| 1 |+------+1 row in set (0.00 sec)mysql> alter table wison_test2 modify id int(5) zerofill;
Query OK, 1 row affected (0.58 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from wison_test2;
+-------+| id |+-------+| 00001 |+-------+1 row in set (0.00 sec)mysql> insert into wison_test2 select 1111111;
Query OK, 1 row affected (0.10 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from wison_test2;
+---------+| id |+---------+| 00001 || 1111111 |+---------+2 rows in set (0.00 sec)3.BIT类型---直接读取会查询不出结果,需要使用hex()或者bin()mysql> create table wison_test3(id bit(2));Query OK, 0 rows affected (0.11 sec)mysql> insert into wison_test3 select 1;
Query OK, 1 row affected (0.07 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from wison_test3;
+------+| id |+------+| |+------+1 row in set (0.00 sec)mysql> select hex(id) from wison_test3;
+---------+| hex(id) |+---------+| 1 |+---------+1 row in set (0.00 sec)mysql> select bin(id) from wison_test3;
+---------+| bin(id) |+---------+| 1 |+---------+1 row in set (0.00 sec)4.TIMESTAMP类型--在Mysql中会根据当前时区不同返回不同的值mysql> create table wison_test4(id1 timestamp,id2 datetime);Query OK, 0 rows affected (0.09 sec)mysql> show variables like 'time_zone';
+---------------+--------+| Variable_name | Value |+---------------+--------+| time_zone | SYSTEM |+---------------+--------+1 row in set (0.00 sec)mysql> insert into wison_test4 select now(),now();
Query OK, 1 row affected (0.07 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from wison_test4;
+---------------------+---------------------+| id1 | id2 |+---------------------+---------------------+| 2014-04-29 23:05:03 | 2014-04-29 23:05:03 |+---------------------+---------------------+1 row in set (0.00 sec)mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)mysql> select * from wison_test4;
+---------------------+---------------------+| id1 | id2 |+---------------------+---------------------+| 2014-04-30 15:05:03 | 2014-04-29 23:05:03 |+---------------------+---------------------+1 row in set (0.00 sec)mysql> set time_zone='-7:00';
Query OK, 0 rows affected (0.00 sec)mysql> select * from wison_test4;
+---------------------+---------------------+| id1 | id2 |+---------------------+---------------------+| 2014-04-29 23:05:03 | 2014-04-29 23:05:03 |+---------------------+---------------------+1 row in set (0.00 sec)5.CHAR数据类型---CHAR会自动将后面的空格截断,VARCHAR会保留空格mysql> create table wison_test5(c1 char(5),c2 varchar(5));Query OK, 0 rows affected (0.11 sec)mysql> insert into wison_test5 select 'a ','a ';
Query OK, 1 row affected (0.08 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from wison_test5;
+------+------+| c1 | c2 |+------+------+| a | a |+------+------+1 row in set (0.00 sec)mysql> select length(c1),length(c2) from wison_test5;
+------------+------------+| length(c1) | length(c2) |+------------+------------+| 1 | 3 |+------------+------------+1 row in set (0.00 sec)mysql> select concat(c1,'+'),concat(c2,'+') from wison_test5;
+----------------+----------------+| concat(c1,'+') | concat(c2,'+') |+----------------+----------------+| a+ | a + |+----------------+----------------+1 row in set (0.00 sec)6.ENUM类型---插入的值可以是在枚举的列表中,可以写明值,或者对应的顺序编号(比如3的意思就是第三个值),NULL(插入之后仍为NULL),传入不存在的值插入的是空格mysql> create table wison_test6(country ENUM('CN','US','TW','RU','BR'));Query OK, 0 rows affected (0.09 sec)mysql> insert into wison_test6 values ('CN'),('US'),('1'),('3'),('4'),(2),(NULL);Query OK, 7 rows affected (0.06 sec)Records: 7 Duplicates: 0 Warnings: 0mysql> select * from wison_test6;
+---------+| country |+---------+| CN || US || CN || TW || RU || US || NULL |+---------+7 rows in set (0.00 sec)mysql> insert into wison_test6 select 'CA';
Query OK, 1 row affected, 1 warning (0.06 sec)Records: 1 Duplicates: 0 Warnings: 1mysql> show warnings;
+---------+------+----------------------------------------------+| Level | Code | Message |+---------+------+----------------------------------------------+| Warning | 1265 | Data truncated for column 'country' at row 1 |+---------+------+----------------------------------------------+1 row in set (0.00 sec)mysql> select * from wison_test6;
+---------+| country |+---------+| CN || US || CN || TW || RU || US || NULL || |+---------+8 rows in set (0.00 sec)另一个例子----可以看到当枚举值背身为数字时,当insert的value没包含'',则表示是第几个枚举值,否则插入的值为insert的对应值.
mysql> create table wison_test7(id ENUM('1','2','4','3','5'));Query OK, 0 rows affected (0.07 sec)mysql> insert into wison_test7 values('1'),('3'),(3),('4'),(4);
Query OK, 5 rows affected (0.07 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> select * from wison_test7;
+------+| id |+------+| 1 || 3 || 4 || 4 || 3 |+------+5 rows in set (0.00 sec)7.在Mysql中，如果列上有索引，无需先Drop Index，可直接修改列。这点与MSSQL不一致。如下：
mysql> create table test1(id int,name char(5));
Query OK, 0 rows affected (0.30 sec)mysql> create index ix_test1_name on test1(name);
Query OK, 0 rows affected (0.21 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table test1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| test1 | CREATE TABLE `test1` ( `id` int(11) DEFAULT NULL, `name` char(5) DEFAULT NULL, KEY `ix_test1_name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> alter table test1 modify name char(10);
Query OK, 0 rows affected (0.54 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table test1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+| test1 | CREATE TABLE `test1` ( `id` int(11) DEFAULT NULL, `name` char(10) DEFAULT NULL, KEY `ix_test1_name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)