Các bộ máy lưu trữ dữ liệu bảng và kiểu dữ liệu trong mysql


tự học mysql 10/09/2015 Cùng chuyên mục

Mysql hỗ trợ lưu trữ bảng với nhiều engine như InnoDB, MyISAM, ... Các kiểu dữ liệu trong mysql như varchar - integer - datetime -...

Mysql Engine storage - Bộ máy lưu trữ dữ liệu

Các engine storage - gọi là bộ máy lưu trữ, sau này để ngắn gọn chúng gọi là kiểu lưu trữ bảng - cung cấp các phương thức làm việc với dữ liệu của bảng. Hỗ trợ 2 hình thức xử lý sự an toàn dữ liệu bảng: giao dịch (transaction) và không giao dịch. Từ phiên bản 5.5.5, kiểu lưu trữ InboDB được chọn là kiểu lưu trữ mặc định cho các bảng. 


Mô hình tương tác ứng dụng - mysql server

Danh sách các bộ máy lưu trữ dữ liệu của Mysql

  • InnoDB
  • MyISAM
  • Memory
  • ARCHIVE
  • BLACKHOLE
  • MERGE
  • FEDERATED
  • EXAMPLE

Trong đó được sử dụng nhiều và phổ biến là InnoDB và MyISAM.

InnoDB

Tính năng chính

  • Điểm khác biệt lớn của InnoDB so với các kiểu khác, nó hỗ trợ giao dịch gồm các quá trình như: commit (đảm bảo thành công) - rollback (quay trở lại nếu không thành công) - crash-recovery: khôi phục dữ liệu khi xảy ra sự cố...nhằm đảm bảo sự an toàn cho dữ liệu của người dùng.
  • row locking
  • Hỗ trợ tổ chức dữ liệu thông qua khóa chính (PRIMARY KEY)- tối ưu hóa truy xuất dữ liệu thông qua khóa chính và các khóa phụ
  • Đảm bảo tính toàn vẹn dữ liệu, hỗ trợ khóa ngoại (FOREIGN KEY) - các thao tác thêm - cập nhật - xóa liên quan đến khóa đều sẽ được kiểm tra.
  • Có thể dùng phép kết (JOIN) với các bảng có kiểu lưu trữ khác.
  • InnoDB được thiết kế tận dụng tối đa hiệu suất của CPU - đặc biệt khi xử lý một khối dữ liệu lớn.

MyISAM

Được sử dụng nhiều cho website - hỗ trợ nhiều tính năng của Mysql - đặc biệt là khả năng lưu trữ (256TB) - trong khi InnoDB chỉ là 64TB - một tính năng khác là FullText Index ( hỗ trợ tìm kiếm toàn bản văn - tìm kiếm chính xác - trong các phiên bản mới InnoDB cũng có hỗ trợ tính năng này nhưng chưa được hoàn hảo như MyISAM) - MyISAM hỗ trợ khóa dữ liệu ở mức bảng, không hỗ trợ khóa ngoại.

Một số kiểu lưu trữ khác:

  • Memory: lưu trực tiếp dữ liệu lên bộ nhớ RAM, thường dùng
    để lưu trữ các dữ liệu tạm.
  • ARCHIVE: dùng trong việc lưu trữ dữ liệu có kích thước lớn
    mà không đánh chỉ mục (indexes) - một cách nhỏ gọn
  • BLACKHOLE: chỉ dùng cho mục đích ghi dữ liệu

Lựa chọn kiểu lưu trữ

- Đối với ứng dụng có ít dữ liệu như Blog - Web tin tức - ... lưu trữ,  ít đòi hỏi tính an toàn bạn có thể lựa chọn MyISAM, đặc biệt để lưu trữ các loại văn bản như bài viết...( sử dụng tính năng FULLTEXT SEARCH INDEX)
- Đối với ứng dụng phải xử lý khối lượng dữ liệu lớn - sử dụng nhiều đến khóa - đảm bảo tính an toàn như giao dịch ngân hàng - thương mại điện tử,..., nên dùng InnoDB
- Đối với các thông tin lưu tạm làm bộ đệm sử dụng kiểu Memory
- Lưu trữ các dữ liệu (kiểu lưu kho) dùng kiểu ARCHIVE
- Tối ưu cho việc ghi dữ liệu sử dụng BLACKHOLE

để chỉ định kiểu lưu trữ cho bảng bạn thêm từ khóa ENGINE = kiểu lưu trữ sau câu lệnh CREATE TABLE...(). Ví dụ: Tạo bảng user có kiểu lưu trữ InnoDB:

CREATE TABLE user(
user_id int unsigned not null auto_increment primary key,
user_name varchar(80)) ENGINE = InnoDB;

CÁC KIỂU DỮ LIỆU TRONG PHP

Mysql hỗ trợ các kiểu dữ liệu chia thành các nhóm sau:
- Nhóm kiểu dữ liệu số (numeric)
- Nhóm kiểu dữ liệu chuỗi (string)
- Nhóm kiểu dữ liệu ngày giờ (datetime)

Nhóm kiểu dữ liệu số:

  • Nhóm số nguyên: INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT
    (!) Khác với PHP, mysql hỗ trợ có dấu và không có dấu (unsigned)
  • Số thập phân (chính xác) DECIMAL 
  • Dấu chấm động: FLOAT, DOUBLE
Kiểu dữ liệu Kích thước Gía trị nhỏ nhất Gía trị lớn nhất 
  (BYTES)   ( có dấu/ không dấu)
TINYINT 1 -128 127
    0 256
SMALLINT 2 -32768 32767
    0 65535
MEDIUMINT 3 -8388608 8388607
    0 16777215
INT 4 -2147483648 2147483647
    0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
    0  18446744073709551615

Kiểu DECIMAL ( Số Thập Phân)

DECIMAL(5,2) - nghĩa là kích thước 5 chữ số với 2 chữ số sau hàng thập phân ví dụ: 5.00 ; 147.48; số 2 còn được hiểu là tính chính xác đến 2 con số sau hàng thập phân (phần 10) - Mặc định: DECIMAL(M,0) - trong đó M nhận giá trị mặc định là 10; chiều dài tối đa các con số của kiểu DECIMAL là 65.

Kiểu Số Thực

Gía trị mang tính xấp xỉ - nếu số lượng số sau khi đã làm tròn từ 0 -> 23 thì kích thước (của trường dữ liệu - column hay field) chiếm khoảng 4 byte cho kiểu FLOAT, nếu số lượng chữ số sau làm tròn từ 23 -> 53 thì kích thước khoảng 8 byte, kiểu DOUBLE kích thước cố định 8 byte ( DOUBLE dùng cho dạng số mũ như 1.2e-8 = 1.2^-8 ). Cú pháp khai báo FLOAT (M,N) trong đó M tổng số chữ số .  và N chữ số ở hàng thập phân.

Ví dụ: nếu bạn khai báo kiểu dữ liệu FLOAT(7,4) - dữ liệu có thể là -999.9999 - nếu dữ liệu nhận được cho cột được khai báo có dạng 999.00009 - mysql sẽ tự động làm tròn thành 999.0001

Bạn có thể đặt giá trị độ dài - n cho các kiểu số, để giới hạn miền giá trị, kích thước. Ví dụ: INT(3) - miền giá trị -2^8 đến (2^8 -1 ) : -32768 đến 32767, (không dấu - unsigned): 0 - 65535. Kích thước của trường dữ liệu thay đổi theo số lượng "chữ số" n. 

Giá trị n Kích thước (byte)
0 0
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4

KIỂU CHUỖI

Chuỗi ký tự

  • Chuỗi có độ dài cố định: CHAR(N)
  • Độ dài thay đổi: VARCHAR(N)

Gía trị mặc định của N=1, một ký tự chiếm 1 byte, với kiểu độ dài thay đổi - N được hiểu là số ký tự tối đa nhưng kích thước được tính theo số ký tự đang có. Ví dụ: một cột có kiểu VARCHAR(3) - có dữ liệu là 'abc' kích thước được tính là 3 byte; còn nếu với kiểu CHAR(3) dù có 2 ký tự, 1 ký tự hay không có ký tự nào vẫn chiếm 3 byte.

Trong các ứng dụng thường sử dụng kiểu CHAR hay VARCHAR cho các thông tin ngắn như Họ Tên - Mô tả ngắn gọn - tên danh mục - tên sách - ... ( nói chung độ dài chuỗi lưu trữ dưới 255 ký tự ). Khi muốn lưu trữ dữ liệu với độ dài lớn - chẳng hạn như nội dung một bài viết - ... sử dụng các kiểu khác như TEXT, BLOB, ...

Kiểu dữ liệu Kích thước
CHAR(M) 0 <= M <= 255
  M byte
BINARY(M) - chuỗi số nhị phân 0 <= M <= 255
  M byte
VARCHAR(M) VARBINARY(M) L+1 byte nếu số ký tự đang có nhỏ hơn 255 - L+2 byte nếu số ký tự đang có lớn hơn 255 với L là số ký tự đang có trong trường dữ liệu
TINYBLOB -TINYTEXT L + 1 byte, khi L < 2^8 - ( vượt quá con số này - L+2)
BLOB, TEXT L + 2 byte, khi L < 2^16 - ( tương tự )
MEDIUMBLOB, MEDIUMTEXT L+3 byte, khi L < 2^24 - (...)
LONGBLOB, LONGTEXT L+4 byte, khi L < 2^32 - (...)
ENUM('value1','value2',...) 1 hoặc 2 byte, tùy theo số lượng giá trị - tối đa 65535 value
SET('value1','value2',...) 1, 2, 3, 4, 8 - tùy thuộc vào value - tối đa 64 value ( phần tử )

KIỂU NGÀY GIỜ

DATE - lưu trữ năm-tháng-ngày định dạng "YYYY-MM-DD" - miền giá trị từ '1000-01-01' đến '9999-12-31' 
DATETIME
- lưu trữ năm-tháng-ngày giờ:phút:giây định dạng "YYYY-MM-DD HH:MM:SS" - miền giá trị từ '1000-01-01 00:00:00' đến '9999-12-31 23:59:59'
TIMESTAMP - định dạng tương tự nhưng miền giá trị từ '1970-01-01 00:00:01' đến '2038-01-19 03:14:07'
TIME -
dùng để lưu trữ giờ-phút-giây ('HHH:MM:SS'), miền giá trị từ '-838:59:59' đến '838:59:59' 
YEAR -
dùng để lưu trữ năm với 2 chữ số, 4 chữ số như 2010 - 60 (những năm 60 của thế kỷ 20)

Kiểu dữ liệu Kích thước (byte)
DATE
TIME 3
DATETIME 8
TIMESTAMP 4
YEAR 1


Lưu ý:
Bạn cần biết định dạng ngày giờ trong Mysql để lưu dữ liệu từ ứng dụng (PHP) với định dạng tương ứng

TỔNG KẾT

Bài này giới thiệu các bộ máy lưu trữ dữ liệu trong Mysql - để bạn có thể lựa chọn khi tạo bảng - tương tự là các kiểu dữ liệu. Khi tìm hiểu các kiểu dữ liệu bạn xem xét các đặc điểm: định dạng - miền giá trị - kích thước, để khi tạo bảng phù hợp với yêu cầu ( giá trị cần lưu trữ nằm trong miền giá trị - (*) không lãng phí bộ nhớ - không lưu trữ sai định dạng ).
Bài kế tiếp, các bạn sẽ học các câu lệnh (statement) tạo cơ sở dữ liêu - tạo bảng - tạo khóa chính (primary key) - khóa ngoại (foregin key) - để học tốt các bạn cần nắm vững bài học này.




Bình luận:


cơ sở dữ liệu mysql kiểu dữ liệu