MySQL 笔记（一）

来自 stackoverflow 的内容居多。

1- MySQL VARCHAR size? 

2- 数据库设计范式

3- What is InnoDB and MyISAM in MySQL ?

4- 为什么手写DDL？

5- Data access object (DAO) in Java

MySQL VARCHAR size? 

Q:

I'm wondering, if I have a VARCHAR of 200 characters and that I put a string of 100 characters, will it use 200 bytes or it will just use the actual size of the string?

A:

100 characters.

This is the var (variable) in varchar: you only store what you enter (and an extra 2 bytes to store length upto 65535)

If it was char(200) then you'd always store 200 characters, padded with 100 spaces

See the docs: "The CHAR and VARCHAR Types"  ------- by gbn

补充:

VARCHAR(M)是一种比CHAR更加灵活的数据类型，同样用于表示字符数据，但是VARCHAR可以保存可变长度的字符串。其中M代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。因此，对于那些难以估计确切长度的数据对象来说，使用VARCHAR数据类型更加明智。MySQL4.1以前,VARCHAR数据类型所支持的最大长度255,5.0以上版本支持65535字节长度,utf8编码下最多支持21843个字符(不为空)  ------ 来自百度百科

 

数据库设计范式

如何能设计出优秀的数据库呢？专家们经过多年研究终于·····  一般按照范式来进行数据库设计就得到良好的数据库，范式的目的在于：消除数据冗余和编程便利。

第一范式就是得到纯二维表， // 只有纯二维的表才能装进数据库

第二范式是消除非主键依赖关系， // “有些列并不直接依赖于主键”，就是说和主键没关系的东西应该扔到另外一张表中去。

第三范式是消除函数依赖关系。// 能算出来的就别额外拿一列装了。。。

做范式的主要手段是“拆表”，但是要它有副作用，······，所以要在数据冗余和编码容易之间寻找平衡点，到了第三范式，基本上就是平衡点了。  ------ 《 Java 就该这样学》

What is InnoDB and MyISAM in MySQL ?

InnoDB and MYISAM, are storage engines for MySQL.

These two differ on their locking implementation: InnoDB locks the particular row in the table, and MyISAM locks the entire MySQL table.

You can specify the type by giving MYISAM OR InnoDB while creating a table in DB.    ------ by Siva

为什么手写 DDL （Data Definition Language）？

schema.sql（demo）:

-- 数据库初始化脚本

-- 创建数据库
CREATE DATABASE chat;
-- 使用数据库
use chat;

-- 创建表
CREATE TABLE user(
  user_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户id',
  username VARCHAR(40) NOT NULL COMMENT '用户名（邮箱）',
  password VARCHAR(40) NOT NULL COMMENT '密码',
  nickname VARCHAR(20) NOT NULL COMMENT '昵称',
  other VARCHAR(120) COMMENT '其他',
  PRIMARY KEY (user_id)
)ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT='用户基本信息';

CREATE TABLE friend_relation(
  user_id BIGINT NOT NULL COMMENT '用户id',
  friend_id BIGINT NOT NULL COMMENT '好友id'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='好友关系表';

CREATE TABLE group_relation(
  user_id BIGINT NOT NULL COMMENT '用户id',,
  group_id BIGINT NOT NULL COMMENT '群组id'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='群组关系表';

-- 初始化数据
INSERT INTO
  'user'('username', 'password', 'nickname', 'other')
VALUES
  ('11122@gmail.com', 123456, '老大', '这人很懒，啥也没写'),
  ('jhgm49@163.com', 123456, '老二', '平平淡淡才是真');

-- INSERT INTO
--   'friend_relation'('user_id', 'friend_id')
-- VALUES


-- 为什么手写 DDL ( Data Definition Language )
-- 记录每次上线的 DDL 修改
-- 上线 V 1.1
-- xxx
-- 上线 V 1.2
-- xxx x x

MySQL 如果表存在就删掉：

DROP TABLE IF EXISTS tbl_name;

Data access object (DAO) in Java

Q:

I was going through a document and I came across a term called DAO. I found out that it is a Data Access Object. Can someone please explain me what this actually is?

I know that it is some kind of an interface for accessing data from different types of sources, in the middle of this little research of mine I bumped into a concept called data source or data source object, and things got messed up in my mind.

I really want to know what a DAO is programmatically in terms of where it is used. How it is used? Any links to pages that explain this concept from the very basic stuff is also appreciated.  ------ by Vasanth Nag K V

A:

The Data Access Object is basically an object or an interface that provides access to an underlying database or any other persistence storage.

That definition from: http://en.wikipedia.org/wiki/Data_access_object

Check also the sequence diagram here: http://www.oracle.com/technetwork/java/dataaccessobject-138824.html

Maybe a simple example can help you understand the concept:

Let's say we have an entity to represent an employee:

public class Employee {

    private int id;
    private String name;


    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

}

The employee entities will be persisted into a corresponding Employee table in a database. A simple DAO interface to handle the database operation required to manipulate an employee entity will be like:

interface EmployeeDAO {

    List<Employee> findAll();
    List<Employee> findById();
    List<Employee> findByName();
    boolean insertEmployee(Employee employee);
    boolean updateEmployee(Employee employee);
    boolean deleteEmployee(Employee employee);

}

Next we have to provide a concrete implementation for that interface to deal with SQL server, and another to deal with flat files, etc.  ------ by Rami