mysql create table primary key auto_increment

mysql> create table ss(id int unsigned not null primary key auto_increment, user_name varchar(15) not null);
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> insert into ss(id,user_name) values(1, 'jojo');
Query OK, 1 row affected (0.00 sec)

mysql> insert into ss(id,user_name) values(37, 'liz');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select id, user_name from ss;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | jojo      |
| 37 | liz       |
+----+-----------+
2 rows in set (0.00 sec)

mysql>

===========================================

mysql> create table uu(id int unsigned not null primary key auto_increment, user_name varchar(15) not null) auto_increment=100;
Query OK, 0 rows affected (0.01 sec)

mysql>

=================================

mysql> insert into uu(id,user_name) values(1, 'jojo');
Query OK, 1 row affected (0.00 sec)

mysql> insert into uu(id,user_name) values(37, 'liz');
Query OK, 1 row affected (0.00 sec)

mysql> select id, user_name from uu;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | jojo      |
| 37 | liz       |
+----+-----------+
2 rows in set (0.00 sec)

mysql> desc uu
    -> ;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(15)      | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql>

当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值

把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做，还是以插入NULL值为好。

如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序 列编号。编号从1开始，并1为基数递增。

因为上面指定了auto_increment=100，所以编号从100开始，递增1。

mysql> insert into uu(user_name) values('Nicky');
Query OK, 1 row affected (0.00 sec)

mysql> select id, user_name from uu;
+-----+-----------+
| id  | user_name |
+-----+-----------+
|   1 | jojo      |
|  37 | liz       |
| 100 | Nicky     |
+-----+-----------+
3 rows in set (0.00 sec)

mysql> insert into uu(user_name) values('Lucy');
Query OK, 1 row affected (0.00 sec)

mysql> select id, user_name from uu;
+-----+-----------+
| id  | user_name |
+-----+-----------+
|   1 | jojo      |
|  37 | liz       |
| 100 | Nicky     |
| 101 | Lucy      |
+-----+-----------+
4 rows in set (0.00 sec)

mysql>  

mysql建表设置两个默认CURRENT_TIMESTAMP的技巧

http://stackoverflow.com/questions/267658/having-both-a-created-and-last-updated-timestamp-columns-in-mysql-4-0

create table t (ids int not null auto_increment primary key, 
name1 varchar(20),
t1 timestamp default '0000-00-00 00:00:00', 
t2 timestamp default now() on update now())

insert into t(name1) values ('tom')

insert into t(name1,t1, t2) values ('jerry', null, null)