MySQL小抄

[RammusXu]

觀念

• primary key 不用再建 index
• primary key 盡量不要修改
  • http://stackoverflow.com/questions/3838414/can-we-update-primary-key-values-of-a-table#answer-3838649
  • 如果影響到很多 table，而且改 table 沒有設 index，將會消耗許多資源
• compound index 建立後，在搜尋時只要符合由左至右的條件，都會用到同樣的 compound index
  • http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html
• ENUM 的使用情境
  • 如果要存數字，就用 tinyint 而非 ENUM
  • rename ENUM value
  • Alter ENUM value、update value、Alter 舊的ENUM value
  • http://stackoverflow.com/questions/15642695/mysql-change-a-column-enum-value

    小技巧

• EXPLAIN
  • http://dev.mysql.com/doc/refman/5.7/en/using-explain.html
• \G 可以將表格轉成直式顯示

  常用指令

show databases;
show tables;

alter table employee change `status` `status` enum('CREATED','VERIFIED','DELETED') default 'CREATED';
alter table employee add column brand_id bigint not null;
alter table user drop index uq_user_email;

Foriegn Key

ALTER TABLE user_info
ADD CONSTRAINT fk_Cusomer_Id FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE;
-- ADD FOREIGN KEY (user_id) REFERENCES user(id);

delete from user where user_id=1;

Primary Key

alter table app_version drop primary key;
alter table app_version change app_version_id app_version_id bigint not null auto_increment primary key;

CRUD

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

Charset

SHOW TABLE STATUS

ALTER TABLE etape_prospection
  CHARACTER SET utf8,
  COLLATE utf8_unicode_ci;

[RammusXu]

Ebean

• OneToMany 使用 UUID 會有錯誤?!
• update() 才會 cascade 刪除物件
• save() 只會 new/ update 不會 delete

使用 timestamp

    @Temporal(TemporalType.TIMESTAMP)
    @Column(updatable = false,
            insertable = false,
            columnDefinition = "timestamp NULL DEFAULT CURRENT_TIMESTAMP")
    public Date create_at = Calendar.getInstance().getTime();

    @Column(updatable = false,
            insertable = false,
            columnDefinition = "timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
    @Temporal(TemporalType.TIMESTAMP)
    public Date update_at = Calendar.getInstance().getTime();

使用 compound key (index / unique)

@Entity
@Table(
        uniqueConstraints = {@UniqueConstraint(columnNames = {"brand_id", "account"})}
)
@Index(columnNames = {"status", "brand_id"})
public class User extends Model {

使用 compound primary key

package models.pk;

import javax.persistence.Embeddable;
import java.io.Serializable;

@Embeddable
public class MyPK implements Serializable {
    public String name;
    public String email;

    @Override
    public boolean equals(Object o) {

        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        MyPK embId = (AppVersionPK) o;

        if (!name.equals(embId.name)) return false;
        if (!email.equals(embId.email)) return false;

        return true;
    }

    @Override
    public int hashCode() {
        int result = name.hashCode();
        result = 31 * result + email.hashCode();
        return result;
    }
}

使用 MyPK

@Entity
public class MyModel extends Model {
    @EmbeddedId
    public MyPK id;

}

使用 Enum

public class User extends Model {
    public enum UserStatus {
        NEW,
        DELETED,
        VERIFIED
    }

    @Enumerated(EnumType.STRING)
    @Column(columnDefinition = "enum('NEW','DELETED','VERIFIED') default 'NEW'")
    public UserStatus status = UserStatus.NEW;
}

使用 sql query

String sql = "select COUNT(*) as count from schema_version";
Long count = Ebean.createSqlQuery(sql).findUnique().getLong("count");