BLOB Sütun Tipleri

Giriş

4 tane BLOB tip var. Bunlar şöyle

BLOB         A small BLOB

TINYBLOB         A very small BLOB (binary large object)

MEDIUMBLOB A medium-sized BLOB

LONGBLOB         A large BLOB

Depolama

Açıklaması şöyle. 4 çeşit depolama yöntemi var

DYNAMIC , REDUNDANT , COMPACT , COMPRESSED 

Another aspect is the MySQL’s storage row format. Depending on row format, MySQL stores data differently.

DYNAMIC row format for BLOB stores 20-byte pointer to where it’s stored. As opposed to REDUNDANT and COMPACT row formats, which store first 768 bytes in a table and the rest in overflow pages. In this benchmark data sizes start from 1028 bytes, so this kind of optimization is irrelevant.

COMPRESSED row format enables compression on database level, so 20-byte pointer will point to another storage which will be compressed using deflate with 4-byte length prefix. For the benchmark sake we will use the same algorithm on application level to see the overhead of MySQL over compression. You may find useful another blog post with overview of MySQL compression.

Örnek

Şöyle yaparız

CREATE TABLE `uncompressed_blobs` (
  `id` INT NOT NULL PRIMARY KEY,
  `data` MEDIUMBLOB NOT NULL
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

Açıklaması şöyle

In this table we will store either uncompressed data or data compressed on an application level without using MySQL compression (obviously, we want to avoid double compression as it ineffective).

Örnek

Şöyle yaparız

CREATE TABLE `compressed_blobs` (
  `id` INT NOT NULL PRIMARY KEY,
  `data` MEDIUMBLOB NOT NULL
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

Sütun Tipleri - JSON

Örnek

Şöyle yaparız

CREATE TABLE `kv` (
  `key` varchar(32) NOT NULL PRIMARY KEY,
  `val` JSON,
);

INSERT INTO kv
  SET `key` = 'test', val = '{"name": "val"}'
  ON DUPLICATE KEY UPDATE val = '{"name": "val"}';

SELECT val FROM kv WHERE `key` = 'test' LIMIT 1;

DROP USER

Örnek

Şöyle yaparız

DROP USER 'user'@'host';

Sütun Tipleri - TIMESTAMP ve JDBC

Kullanım Özeti

- Ben Connector/J 8 ve üstünü kullanıyorum ve hem MySQL sunucusunun hem de uygulamamın saat dilimi UTC. Başka da bir şey yapmama gerek kalmadı.

- Eğer uygulamamın saat dilimini UTC yapamıyorsam, bağlantıya "preserveInstants=true&connectionTimeZone=UTC" eklemek yeterli.

- Eğer Hibernate kullanıyorsam şöyle yaparız

# JPA
<property name="hibernate.jdbc.time_zone" value="UTC"/>

#Spring
spring.jpa.properties.hibernate.jdbc.time_zone=UTC

1. Bağlantı string'i

İki kullanım şekli var

- preserveInstants=true verilir ve bağlantıda belirtilen saat diliminin kullanılması istenir

- preserveInstants=false verilir ve JVM'in saat diliminin kullanılması istenir

 - Eğer Bağlantı string'inde saat dilimi verilmemişse JVM saat dilimi kullanılır

Örnek 

UTC için JDBC bağlantısına "preserveInstants=true&connectionTimeZone=UTC" eklenir. preserveInstants=true yerelleştirme ve belirttiğim saat dilimini kullanma anlamına geliyor.

1.1 preserveInstants=true + Saat Dilimi

preserveInstants=true için açıklama şöyle. JVM'in saat dilimi değil, bağlantıda belirtilen ayarlar kullanılıyor

When preserveInstants is true, Connector/J attempts to preserve the time instants by performing the conversions in a manner defined by the connection properties connectionTimeZone and forceConnectionTimeZoneToSession.

preserveInstants=true için bir örnek şöyle. Bu sefer değerler yerelleştirildi

Use preserveInstants=true&connectionTimeZone=SERVER . Connector/J then queries the value of the session time zone from the server, and converts the event timestamps between the session time zone and the JVM time zone. For example:

Time zones: UTC+2 for JVM, UTC+1 for server session

Original timestamp from client (in UTC+2): 2020-01-01 02:00:00

Timestamp sent to server by Connector/J: 2020-01-01 01:00:00 (after conversion from UTC+2 to UTC+1)

Timestamp value stored internally on the server: 2020-01-01 00:00:00 UTC (after internal conversion from UTC+1 to UTC)

Timestamp value retrieved later into a server session in UTC+1: 2020-01-01 01:00:00 (after internal conversion from UTC to UTC+1)

Timestamp values constructed by Connector/J in the same JVM time zone as before (UTC+2) and returned to an application: 2020-01-01 02:00:00 (after conversion from UTC+1 to UTC+2)

Timestamp values constructed by Connector/J in another JVM time zone (say, UTC+3) and returned to an application: 2020-01-01 03:00:00 (after conversion from UTC+1 to UTC+3)

Comment: Time instant is preserved.

Örnek

Şöyle yaparız. Kayıt şöyle '1970-01-01 00:00:01.000000'. Burada bağlantıda verilen UTC ayar kullanılır ve TimeStamp UTC'ye göre string'e çevrilir.

dataSource.setUrl("jdbc:mysql://172.18.17.209:32110/ADV
?preserveInstants=true&connectionTimeZone=UTC");
...
new Timestamp(1000)

1.2 preserveInstants=false

preserveInstants=false ise açıklaması şöyle. Yani JVM'in saat dilimine göre string'e çevrilir ve gönderilir. Ancak Connector/J 8.0.23 ile varsayılan davranış bu değil. Varsayılan davranış preserveInstants=true

With ‘preserveInstants=false’ Connector/J 8.0.23 always uses the JVM default time zone for rendering the values it sends to the server and for constructing the Java objects from the fetched data. It matches the default Connector/J 5.1 behavior. If ‘preserveInstants=true’ (which is the default value), Connector/J does its best to preserve the instant point on the time-line for Java instant-based objects such as java.sql.Timestamp or java.time.OffsetDateTime, instead of preserving the time’s original visual form.

preserveInstants=false için bir örnek şöyle. Yani gönderilen ve alınan değer hep aynı çıktı. Yerelleştirilmedi

Time zones: UTC for JVM, UTC+1 for server session

Original timestamp from client (in UTC): 2020-01-01 01:00:00

Timestamp sent to server by Connector/J: 2020-01-01 01:00:00 (no conversion)

Timestamp values stored internally on the server: 2020-01-01 00:00:00 UTC 

Timestamp value retrieved later into a server section (in UTC+1): 2020-01-01 01:00:00 

Timestamp values constructed by Connector/J in some other JVM time zone then before (say, in UTC+3): 2020-01-01 01:00:00

Comment: Time instant is not preserved

2. Eğer Bağlantı string'inde saat dilimi verilmemişse JVM saat dilimi kullanılır

Örnek - JVM GMT + 2 İse

new TimeStamp(0) olarak kaydedince veri tabanı '1970-01-01 02:00:00.000000' olarak gösterdi. Açıklaması şöyle

Internally a MySQL timestamp column is stored as UTC but when selecting a date MySQL will automatically convert it to the current session timezone.

Örnek - JVM UTC İse

Şöyle yapınca kaydetmedi

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
...
new Timestamp(0)

Hata şöyle. Yani 1292 hatasını veriyor. Benzer bir hata da burada.

Incorrect datetime value: '1970-01-01 00:00:00' for column 'EXPIRATION_DATE' at row 1 (errno 1292) (sqlstate 22007)

Şöyle yapınca kaydetti. Kayıt şöyle '1970-01-01 00:00:01.000000'

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
...
new Timestamp(1_000L)

Sütun Tipleri - TIMESTAMP ve time_zone İlişkisi

Giriş

Eğer düz SQL kullanıyorsak TIMESTAMP sütunu time_zone değişkeninden etkilenir. Veri tabanının saat diliminden etkilendiğini görmek için şöyle yaparız.

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+

mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql> set time_zone="america/new_york";

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

2. Varsayılan Değer

Bence kullanım şekli şöyle olmalı

-- Kullanmayın
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

-- Kullanın
ts TIMESTAMP DEFAULT (UTC_TIMESTAMP)

2.1 CURRENT_TIMESTAMP

Varsayılan değer olarak CURRENT_TIMESTAMP() Kullanıyorsak - time_zone'dan etkilenir.

Örnek

Şöyle yaparız. CURRENT_TIMESTAMP ile NOW() aynı şeyler

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);  

Şöyle yaparız. Şu anda bağlantımın time_zone ayarı UTC olduğu için bilgisayarımın saati 10:59 olmasına rağmen, 07:59 olarak kaydediyor.

INSERT INTO t1() VALUES();

SELECT * FROM t1;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2021-11-26 07:59:12 | 2021-11-26 07:59:12 |
+---------------------+---------------------+
1 row in set (0.00 sec)

 time_zone değerini değiştirelim

SET @@session.time_zone = '+03:00';

Bu sefer farklı sonuç alırız.

INSERT INTO t1() VALUES();
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM t1;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2021-11-26 10:59:12 | 2021-11-26 10:59:12 |
+---------------------+---------------------+
1 row in set (0.00 sec)

2.2 UTC_TIMESTAMP

Dolayısıyla bağlantının time_zone ayarı değişikliğinden etkilenmemek için UTC_TIMESTAMP() kullanılır. Ancak bazı eksikler var. Şöyle yapamıyorum, MySQL hata veriyor

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT (UTC_TIMESTAMP) ON UPDATE (UTC_TIMESTAMP),
  dt DATETIME DEFAULT (UTC_TIMESTAMP) ON UPDATE (UTC_TIMESTAMP)
);

Ancak şöyle yapabiliyorum. Bu durumda ON UPDATE özelliği olmuyor. Bununla ilgili bir hata açılmış.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT (UTC_TIMESTAMP),
  dt DATETIME DEFAULT (UTC_TIMESTAMP)
);

Precision için şöyle yaparız

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT (UTC_TIMESTAMP),
  dt DATETIME(6) DEFAULT (UTC_TIMESTAMP)
);

Bu sefer çıktı farklıdır

mysql> INSERT INTO t1() VALUES();
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM t1;
+----------------------------+----------------------------+
| ts                         | dt                         |
+----------------------------+----------------------------+
| 2021-11-26 08:17:46.000000 | 2021-11-26 08:17:46.000000 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

Boolean Sütun Tipleri

1. BIT
Şöyle yaparız.

BIT(1) DEFAULT b'1'

2. BOOLEAN

Şöyle yaparız

CREATE TABLE IF NOT EXISTS ADV.ID_GENERATOR
(
    name      VARCHAR(40),
    value     INTEGER,
    increment INTEGER DEFAULT 1,
    max_value INTEGER,
    cyclic    BOOLEAN DEFAULT 1, -- here
    region_id INT
);

Örnek

Şöyle yaparız

CREATE TABLE foo (
  v VARCHAR(100),
  b BOOLEAN,
  ti TINYINT,
  si SMALLINT,
  i INTEGER,
  bi BIGINT,
  dc DECIMAL,
  r REAL,
  dbl DOUBLE,
  tm TIME,
  dt DATE,
  ts TIMESTAMP,
  tstz TIMESTAMP WITH TIME ZONE
);

INT Sütun Tipleri

Giriş

Şöyledir

TINYINT	 	1 Byte		[-128,127] 
SMALLINT	2 Bytes		[-32768,32767]
MEDIUMINT	3 Bytes		[-8388608,8388607} 
INT OR INTEGER	4 Bytes		[-2147483648,2147483647]
BIGINT		8 Bytes		[-2^63,-2^63-1]

Java karşılıkları şöyle

MySQL Type				Java Type
CHAR, VARCHAR, LONGVARCHAR		String
TEXT					String
TINYINT					byte
SMALLINT				short
INT (INTEGER)				int
BIGINT					long
NUMERIC, DECIMAL			java.math.BigDecimal
REAL					float
FLOAT, DOUBLE				double
BIT					boolean
BINARY, VARBINARY, LONGVARBINARY   	byte[]
BLOB, MEDIUMBLOB, LARGEBLOB   		byte[]
DATE					java.sql.Date
TIME					java.sql.Time
DATETIME, TIMESTAMP			java.sql.Timestamp

INT ve Display Width

Açıklaması şöyle

Generally, the numbers after int are only valid when used together with zerofill

Açıklaması şöyle

In MySQL, when you define a column as int(4), the 4 in parentheses is used to specify the display width of the column, not the number of bytes it will use for storage. The display width is used only for formatting output and has no impact on the range of values that can be stored in the column. The range of values that can be stored in an int column is determined by its data type, not by the display width.

Örnek

Şöyle yaparız. Burada int(1) kullanınca halen en büyük sayıyı ekleyebildiğimizi görebiliyoruz

CREATE TABLE user (
  id int(1) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

mysql> INSERT INTO user (idVALUES (4294967295);
Query OK, 1 row affected (0.00 sec)

Nasıl Kullanılır ?

Display Width eğer zerofill ile birlikte kullanılırsa işe yarıyor

Örnek

Şöyle yaparız. Burada tablo yaratılırken zerofill belirtiliyor. Böylece sayılar 4 hane olacak şekilde gerekirse sıfır ile dolduruluyor

CREATE TABLE user (
  id int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

mysql> INSERT INTO `user` (`id`) VALUES (1),(10),(100),(1000);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from user;
+------+
| id   |
+------+
| 0001 |
| 0010 |
| 0100 |
| 1000 |
+------+
4 rows in set (0.00 sec)

1. TINYINT

Java'daki Byte tipine denk gelir.

Örnek

Şöyle yaparız.

tinyint(4) DEFAULT NULL

2. SMALLINT
Java'daki Short tipine denk gelir.

Örnek

Şöyle yaparız.

SMALLINT(5) UNSIGNED NOT NULL AUTO INCREMENT

3. INT

Java'daki Integer tipine denk gelir.

Örnek

Şöyle yaparız.

INT(11)

Kısıt koymak istersek şöyle yaparız.

INT(11) NOT NULL AUTO INCREMENT

Şöyle yaparız.

INT(11) NOT NULL DEFAULT '0'

Şöyle yaparız.

INT(11) DEFAULT NULL

4. INT UNSIGNED

Örnek

Şöyle yaparız.

INT(10) UNSINGNED NOT NULL DEFAULT '0',

Şöyle yaparız.

CREATE TABLE list_relation
(
  parent_id INT UNSIGNED NOT NULL,
  ...
);

5. BIGINT
Java'daki Long tipine denk gelir.
Örnek
Şöyle yaparız.

BIGINT(20) UNSGINED NOT NULL

Örnek
Şöyle yaparız.

CREATE TABLE questions (
  id BIGINT NOT NULL CONSTRAINT questions_pkey PRIMARY KEY,
  user_id BIGINT CONSTRAINT fk_questions_users REFERENCES users,
  question TEXT NOT NULL;
)