mysql create table auto_increment_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的技巧

create table t (ids int not null auto_increment primary key,

name1varchar(20),

t1timestamp default '0000-00-00 00:00:00',

t2timestamp default now() on update now())

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

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