# The default length of Prisma string mapping to MySQL varchar

[Prisma](https://www.prisma.io/docs/guides) 是一套支援 nodejs 與 TypeScript 的 ORM library目前在工作上有使用到它

Prisma 在使用 MySQL 為 database source，轉換資料型態時預設會將 `string` 轉為 `varchar(191)`，這篇筆記主要在紀錄研究這個 __191__ 的為何而來

---

根據 Prisma 的 [Default type mappings](https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#string)，`string` 對應到 MySQL 的 `varchar(191)`

在 Prisma 的 github issues [#623](https://github.com/prisma/prisma/issues/623) [#1940](https://github.com/prisma/prisma/discussions/1940) [#1952](https://github.com/prisma/prisma/issues/1952) [#1975](https://github.com/prisma/prisma/issues/1975) 中可以發現，在 2020 以前， Prisma 並不支援自訂長度的 varchar，並且也不支援大於 191 長度的資料型態轉換(例如： `LONGTEXT`)，這些功能都是在之後陸續加進去的
>相關討論統一收錄在[#446](https://github.com/prisma/prisma/issues/446)

---

## 問題在於，為何是 __191__ ？

在找尋相關問題的時候，在 `stackoverflow` 上找到了[這一篇](https://stackoverflow.com/questions/1814532/mysql-error-1071-specified-key-was-too-long-max-key-length-is-767-bytes)

根據 MySQL 5.6 manual: [13.1.13 CREATE INDEX Statement](https://dev.mysql.com/doc/refman/5.6/en/create-index.html)
>Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled.

也就是說，在 5.6 之前，若是要將 string 類型的資料作為 index，其長度必須小於 767 bytes，而這還不是真正可以儲存的字數，根據 table 的 collation 不同，最大可儲存字數也會不一樣
> 在使用預設 engine 的情形下。若是 `MyISAM` 其長度為 1000 bytes

---

### 實際驗證

為了實際驗證，開了一台 MySQL5.6 的 container 來測試

首先建立一個 table，collation 採用最短的 `latin 1`，所以每個 char 只佔去 __1 byte__


這邊直先給了一個 column `c1`，長度設定為 767，接著嘗試將 c1 加入 index:

執行成功，c1 已經加入 index，長度有過

接著把 index 移除，嘗試把 c1 加長，再重新加入 index:


這邊觀察到有趣的現象，長度增加後，把 index 加回去，卻沒有如預期的失敗，但是出現了一則警告:
:::warning
Warning 1071 Specified key was too long; max key length is 767 bytes
:::

觀察一下 table schema，發現有趣的事


index 建立成功，但是長度依然只能到 767，正如 manual 上說明的:
>For string columns, indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length

關鍵在於 `leading part of column value`，換句話說，長度不超過 767 bytes 的部分，會完整的收錄到 index，超過的部分就會被省略。

接著將實驗改為使用較普遍的`utf8mb4`，也就是一個字會用去 __4個 bytes__


因為 $767 \div 4$ 除不盡，所以最大長度只能取到 __191__ (!?)

因為 $191 \times 4 = 764 < 767$ ，所以加進 index 預期是沒問題


和之前的實驗一樣，將 index 移除，加長 c1 後再把 index 加回去:


如預期的結果，發出警告，觀察一下 table schema:


和之前一樣，只有 leading part 小於 767 bytes 的部分被加進 index

---

關於 index 只包含 leading part 的部分，實際上當 column value 的長度真的超過限制，這時 index 的功能是否正常，能否保證最佳化查詢的效能？考量到實際使用場景，真的將 index 用這樣的長度來儲存的情況不常見

>以一個中文字4 bytes 來算，index 的內容將是長達 764 個中文字的資料

---

### Prisma client

回到 Prisma 的部分，在實驗的過程中發現，若是透過 Prisma 來操作，MySQL 的回應將會是



和 mysql cli 不同， Prisma client，的操作是直接以 error 回應，執行失敗

同樣的以其他 MySQL GUI 來操作，也是得到一樣的結果

---

### 小結

這邊推測，Prisma 在設計初期，可能是以最普遍的使用情境來設定，考量到 string 的值經常被當作 index，就把長度設定在最大值的 191，並且初期還沒有提供調整的彈性

:::info
767 bytes 這個限制，在 MySQL 5.7 之後的版本，增加為 3072 bytes
:::

---

## 延伸：MySQL 欄位最大長度？

根據 MySQL manual [
8.4.7 Limits on Table Column Count and Row Size](https://dev.mysql.com/doc/refman/5.6/en/column-count-limit.html) 的說明，單一列資料(row)的最大長度限制為 65535 bytes:
>The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows.

:::info
最大的欄位(column)數為 4096
:::

若是以 `utf8mb4` 為 collation，因為一個 character 長度是 4 bytes，最大字數將是:
$65535 \div 4 = 16383 ... 3$，也就是 16383 個字，包含了所有的欄位加總

但是以一般的使用情形來說，這樣的長度並不常見，若需要用到特別長的字串型資料，MySQL建議用 `TEXT` 來代替

>題外話，在 MySQL cli 執行以下指令時
>```sql=
>create table t1 (
> c1 varchar(16383) null
>)
> collate = utf8mb4;
>create index mykey on t1 (c1);
>```
>MySQL 會非常貼心的幫忙把 `c1` 的型別轉為`TEXT`

---

### TEXT

根據 MySQL manual [11.6: String Type Storage Requirements](https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html#data-types-storage-reqs-strings)

`TEXT` 與 `VARCHAR` 不同，`VARCHAR` 的資料長度限制是與同一列的其他欄位共享
>The effective maximum number of bytes that can be stored in a VARCHAR or VARBINARY column is subject to the maximum row size of 65,535 bytes, which is shared among all columns.

`TEXT` 與 `BLOB` `JSON` 相同，用的是另一套 `NDB` storage engine，有各自的固定長度，不需與其他欄位共享
>TEXT, BLOB, and JSON columns are implemented differently in the NDB storage engine, wherein each row in the column is made up of two separate parts. One of these is of fixed size (256 bytes for TEXT and BLOB, 4000 bytes for JSON), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which is stored in a hidden blob parts table.

因此對字串類型資料有特殊長度需求，用 `TEXT` 系列會是比較好的選擇

###### tags: `MySQL` `database` `sql`