1 million bytes of storage, as opposed to 6 million bytes Creating and Using ENUM Columns 枚举列

http://dev.mysql.com/doc/refman/5.7/en/enum.html

Creating and Using ENUM Columns

An enumeration value must be a quoted string literal. For example, you can create a table with an ENUM column like this:

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt | medium |
+---------+--------+
UPDATE shirts SET size = 'small' WHERE size = 'large';
COMMIT;

Inserting 1 million rows into this table with a value of 'medium' would require 1 million bytes of storage, as opposed to 6 million bytes if you stored the actual string 'medium' in a VARCHAR column.

 

http://dev.mysql.com/doc/refman/5.7/en/commit.html

 

 1 mysql> create table  student
 2     -> (
 3     ->  sno int primary key,
 4     ->  sname varchar(20) CHARACTER SET utf8,
 5     ->  sage tinyint,
 6     ->  shair ENUM('黑','白','黄') CHARACTER SET utf8
 7     -> );
 8 Query OK, 0 rows affected (0.00 sec)
 9 
10 mysql> insert into student values(1,'张三',23,'黑');
11 Query OK, 1 row affected (0.00 sec)
12 
13 mysql> insert into student values(11,'张三',23,'中');
14 ERROR 1265 (01000): Data truncated for column 'shair' at row 1
15 mysql> select * from student;
16 +-----+-------+------+-------+
17 | sno | sname | sage | shair |
18 +-----+-------+------+-------+
19 |   1 | 张三  |   23 | 黑    |
20 +-----+-------+------+-------+
21 1 row in set (0.00 sec)
22 
23 mysql> show errors;
24 Empty set (0.00 sec)
25 
26 mysql> show warnings
27     -> ;
28 Empty set (0.00 sec)
29 
30 mysql> show count(*) errors;
31 +-----------------------+
32 | @@session.error_count |
33 +-----------------------+
34 |                     0 |
35 +-----------------------+
36 1 row in set (0.00 sec)
37 
38 mysql> insert into student values(1,'张三',23,'白     ');
39 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
40 mysql> insert into student values(13,'张三',23,'白     ');
41 Query OK, 1 row affected (0.00 sec)
42 
43 mysql> insert into student values(123,'张三',23,'    白     ');
44 ERROR 1265 (01000): Data truncated for column 'shair' at row 1
45 mysql> select * from student;
46 +-----+-------+------+-------+
47 | sno | sname | sage | shair |
48 +-----+-------+------+-------+
49 |   1 | 张三  |   23 | 黑    |
50 |  13 | 张三  |   23 | 白    |
51 +-----+-------+------+-------+
52 2 rows in set (0.00 sec)
53 
54 mysql>

 

14.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic:
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

These statements provide control over use of transactions:

  • START TRANSACTION or BEGIN start a new transaction.

  • COMMIT commits the current transaction, making its changes permanent.

  • ROLLBACK rolls back the current transaction, canceling its changes.

  • SET autocommit disables or enables the default autocommit mode for the current session.

 

小结：

0-支持右空白，不支持左空白，php脚本应截去字符串，避免出错且减少数据库负担；

 

发问：

0-怎样积累这样的1点？

posted @ 2016-08-11 18:13  papering  阅读(357)  评论(0)  编辑  收藏  举报