MySQL/MariaDB
MySQL 数据源连接器将 Prisma ORM 连接到 MySQL 或 玛丽亚数据库 数据库服务器。
¥The MySQL data source connector connects Prisma ORM to a MySQL or MariaDB database server.
默认情况下，MySQL 连接器包含负责连接到数据库的数据库驱动程序。你可以使用 驱动适配器（预览版）通过 Prisma 客户端中的 JavaScript 数据库驱动程序连接到数据库。
¥By default, the MySQL connector contains a database driver responsible for connecting to your database. You can use a driver adapter (Preview) to connect to your database using a JavaScript database driver from Prisma Client.
示例
¥Example
要连接到 MySQL 数据库服务器，你需要在 Prisma 架构 中配置 datasource 块：
¥To connect to a MySQL database server, you need to configure a datasource block in your Prisma schema:
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
传递到 datasource 块的字段是：
¥The fields passed to the datasource block are:
-
provider：指定mysql数据源连接器，用于 MySQL 和 MariaDB。¥
provider: Specifies themysqldata source connector, which is used both for MySQL and MariaDB. -
url：指定 MySQL 数据库服务器的 连接网址。在本例中，使用环境变量 提供连接 URL。¥
url: Specifies the connection URL for the MySQL database server. In this case, an environment variable is used to provide the connection URL.
连接详情
¥Connection details
连接网址
¥Connection URL
以下是 MySQL 连接 URL 所需组件的概述：
¥Here's an overview of the components needed for a MySQL connection URL:
基本 URL 和路径
¥Base URL and path
以下是使用大写字母占位符值的基本 URL 和路径的结构示例：
¥Here is an example of the structure of the base URL and the path using placeholder values in uppercase letters:
mysql://USER:PASSWORD@HOST:PORT/DATABASE
以下组件构成数据库的基本 URL，它们始终是必需的：
¥The following components make up the base URL of your database, they are always required:
| 名称 | 占位符 | 描述 |
|---|---|---|
| 主持人 | HOST | 你的数据库服务器的 IP 地址/域，例如 localhost |
| 港口 | PORT | 数据库服务器运行的端口，例如 5432（默认为 3306，或者使用 Unix 套接字时没有端口） |
| 用户 | USER | 你的数据库用户的名称，例如 janedoe |
| 密码 | PASSWORD | 你的数据库用户的密码 |
| 数据库 | DATABASE | 你要使用的 database 的名称，例如 mydb |
你必须 对特殊字符进行百分比编码。
¥You must percentage-encode special characters.
参数
¥Arguments
连接 URL 也可以带参数。以下是与上面相同的示例，其中三个参数的占位符值均采用大写字母：
¥A connection URL can also take arguments. Here is the same example from above with placeholder values in uppercase letters for three arguments:
mysql://USER:PASSWORD@HOST:PORT/DATABASE?KEY1=VALUE&KEY2=VALUE&KEY3=VALUE
可以使用以下参数：
¥The following arguments can be used:
| 参数名称 | 必需的 | 默认 | 描述 |
|---|---|---|---|
connection_limit | 不 | num_cpus * 2 + 1 | 连接池 的最大尺寸 |
connect_timeout | 不 | 5 | 等待新连接打开的最大秒数，0 表示不超时 |
pool_timeout | 不 | 10 | 等待池中新连接的最大秒数，0 表示不超时 |
sslcert | 不 | 服务器证书的路径。证书路径为 相对于 ./prisma folder 已解决 | |
sslidentity | 不 | PKCS12 证书的路径 | |
sslpassword | 不 | 用于保护 PKCS12 文件的密码 | |
sslaccept | 不 | accept_invalid_certs | 配置是否检查证书中的缺失值。可能的值：accept_invalid_certs、strict |
socket | 不 | 指向包含用于连接的套接字的目录 | |
socket_timeout | 不 | 等待单个查询终止的秒数 |
例如，如果要将连接池大小设置为 5 并配置查询超时为 3 秒，则可以使用以下参数：
¥As an example, if you want to set the connection pool size to 5 and configure a timeout for queries of 3 seconds, you can use the following arguments:
mysql://USER:PASSWORD@HOST:PORT/DATABASE?connection_limit=5&socket_timeout=3
配置 SSL 连接
¥Configuring an SSL connection
如果你的数据库服务器使用 SSL，则可以向连接 URL 添加各种参数。以下是可能参数的概述：
¥You can add various parameters to the connection URL if your database server uses SSL. Here's an overview of the possible parameters:
-
sslcert=<PATH>：服务器证书的路径。这是数据库服务器用来签署客户端证书的根证书。如果系统的受信任证书存储中不存在该证书，则需要提供此证书。对于 Google Cloud，这可能是server-ca.pem。证书路径为 相对于./prisma folder已解决¥
sslcert=<PATH>: Path to the server certificate. This is the root certificate used by the database server to sign the client certificate. You need to provide this if the certificate doesn't exist in the trusted certificate store of your system. For Google Cloud this likely isserver-ca.pem. Certificate paths are resolved relative to the./prisma folder -
sslidentity=<PATH>：从客户端证书和密钥创建的 PKCS12 证书数据库的路径。这是 PKCS12 格式的 SSL 身份文件，你将使用客户端密钥和客户端证书生成该文件。它将这两个文件组合在一个文件中，并通过密码保护它们（请参阅下一个参数）。你可以使用以下命令（使用openssl）使用客户端密钥和客户端证书创建此文件：¥
sslidentity=<PATH>: Path to the PKCS12 certificate database created from client cert and key. This is the SSL identity file in PKCS12 format which you will generate using the client key and client certificate. It combines these two files in a single file and secures them via a password (see next parameter). You can create this file using your client key and client certificate by using the following command (usingopenssl):openssl pkcs12 -export -out client-identity.p12 -inkey client-key.pem -in client-cert.pem -
sslpassword=<PASSWORD>：用于保护 PKCS12 文件的密码。上一步中列出的openssl命令将在创建 PKCS12 文件时要求输入密码，你需要在此处提供完全相同的密码。¥
sslpassword=<PASSWORD>: Password that was used to secure the PKCS12 file. Theopensslcommand listed in the previous step will ask for a password while creating the PKCS12 file, you will need to provide that same exact password here. -
sslaccept=(strict|accept_invalid_certs)：-
strict：证书中的任何缺失值都会导致错误。对于 Google Cloud，特别是如果数据库没有域名，证书可能会丢失域名/IP 地址，从而导致连接时出错。¥
strict: Any missing value in the certificate will lead to an error. For Google Cloud, especially if the database doesn't have a domain name, the certificate might miss the domain/IP address, causing an error when connecting. -
accept_invalid_certs（默认）：绕过这个检查。请注意此设置的安全后果。¥
accept_invalid_certs(default): Bypass this check. Be aware of the security consequences of this setting.
-
你的数据库连接 URL 将类似于以下内容：
¥Your database connection URL will look similar to this:
mysql://USER:PASSWORD@HOST:PORT/DATABASE?sslidentity=client-identity.p12&sslpassword=mypassword&sslcert=rootca.cert
通过套接字连接
¥Connecting via sockets
要通过套接字连接到 MySQL/MariaDB 数据库，你必须将 socket 字段作为查询参数添加到连接 URL（而不是将其设置为 URI 的 host 部分）。该参数的值必须指向包含套接字的目录，例如 在 Ubuntu 或 Debian 上默认安装 MySQL/MariaDB 时使用：mysql://USER:PASSWORD@HOST/DATABASE?socket=/run/mysqld/mysqld.sock
¥To connect to your MySQL/MariaDB database via a socket, you must add a socket field as a query parameter to the connection URL (instead of setting it as the host part of the URI).
The value of this parameter then must point to the directory that contains the socket, e.g. on a default installation of MySQL/MariaDB on Ubuntu or Debian use: mysql://USER:PASSWORD@HOST/DATABASE?socket=/run/mysqld/mysqld.sock
请注意，localhost 是必需的，该值本身将被忽略并且可以是任何值。
¥Note that localhost is required, the value itself is ignored and can be anything.
注意：你可以在此 GitHub 问题 中找到更多上下文。
¥Note: You can find additional context in this GitHub issue.
MySQL 到 Prisma 架构之间的类型映射
¥Type mapping between MySQL to Prisma schema
MySQL 连接器将 Prisma ORM 数据模型 中的 标量类型 映射到原生列类型，如下所示：
¥The MySQL connector maps the scalar types from the Prisma ORM data model as follows to native column types:
或者，请参阅 Prisma 架构参考 了解按 Prisma ORM 类型组织的类型映射。
¥Alternatively, see Prisma schema reference for type mappings organized by Prisma ORM type.
从 Prisma ORM 到 MySQL 的原生类型映射
¥Native type mapping from Prisma ORM to MySQL
| Prisma ORM | MySQL | 注意 |
|---|---|---|
String | VARCHAR(191) | |
Boolean | BOOLEAN | 在 MySQL 中 BOOLEAN 是 TINYINT(1) 的同义词 |
Int | INT | |
BigInt | BIGINT | |
Float | DOUBLE | |
Decimal | DECIMAL(65,30) | |
DateTime | DATETIME(3) | 目前，Prisma ORM 不支持 MySQL 中的零日期（0000-00-00、00:00:00） |
Json | JSON | 仅在 MySQL 5.7+ 中受支持 |
Bytes | LONGBLOB |
从 Prisma ORM 到 MariaDB 的原生类型映射
¥Native type mapping from Prisma ORM to MariaDB
| Prisma ORM | 玛丽亚数据库 | 注意 |
|---|---|---|
String | VARCHAR(191) | |
Boolean | BOOLEAN | 在 MariaDB 中 BOOLEAN 是 TINYINT(1) 的同义词 |
Int | INT | |
BigInt | BIGINT | |
Float | DOUBLE | |
Decimal | DECIMAL(65,30) | |
DateTime | DATETIME(3) | |
Json | LONGTEXT | 见 https://mariadb.com/kb/en/json-data-type/ |
Bytes | LONGBLOB |
原生类型映射
¥Native type mappings
内省 MySQL 数据库时，数据库类型根据下表映射到 Prisma ORM：
¥When introspecting a MySQL database, the database types are mapped to Prisma ORM according to the following table:
| MySQL | Prisma ORM | 支持的 | 原生数据库类型属性 | 注意 |
|---|---|---|---|---|
serial | BigInt | ✔️ | @db.UnsignedBigInt @default(autoincrement()) | |
bigint | BigInt | ✔️ | @db.BigInt | |
bigint unsigned | BigInt | ✔️ | @db.UnsignedBigInt | |
bit | Bytes | ✔️ | @db.Bit(x) | bit(1) 映射到 Boolean - 所有其他 bit(x) 映射到 Bytes |
boolean | tinyint(1) | Boolean | ✔️ | @db.TinyInt(1) | |
varbinary | Bytes | ✔️ | @db.VarBinary | |
longblob | Bytes | ✔️ | @db.LongBlob | |
tinyblob | Bytes | ✔️ | @db.TinyBlob | |
mediumblob | Bytes | ✔️ | @db.MediumBlob | |
blob | Bytes | ✔️ | @db.Blob | |
binary | Bytes | ✔️ | @db.Binary | |
date | DateTime | ✔️ | @db.Date | |
datetime | DateTime | ✔️ | @db.DateTime | |
timestamp | DateTime | ✔️ | @db.TimeStamp | |
time | DateTime | ✔️ | @db.Time | |
decimal(a,b) | Decimal | ✔️ | @db.Decimal(x,y) | |
numeric(a,b) | Decimal | ✔️ | @db.Decimal(x,y) | |
enum | Enum | ✔️ | 不适用 | |
float | Float | ✔️ | @db.Float | |
double | Float | ✔️ | @db.Double | |
smallint | Int | ✔️ | @db.SmallInt | |
smallint unsigned | Int | ✔️ | @db.UnsignedSmallInt | |
mediumint | Int | ✔️ | @db.MediumInt | |
mediumint unsigned | Int | ✔️ | @db.UnsignedMediumInt | |
int | Int | ✔️ | @db.Int | |
int unsigned | Int | ✔️ | @db.UnsignedInt | |
tinyint | Int | ✔️ | @db.TinyInt(x) | tinyint(1) 映射到 Boolean 所有其他 tinyint(x) 映射到 Int |
tinyint unsigned | Int | ✔️ | @db.UnsignedTinyInt(x) | tinyint(1) unsigned 不映射到 Boolean |
year | Int | ✔️ | @db.Year | |
json | Json | ✔️ | @db.Json | 仅在 MySQL 5.7+ 中受支持 |
char | String | ✔️ | @db.Char(x) | |
varchar | String | ✔️ | @db.VarChar(x) | |
tinytext | String | ✔️ | @db.TinyText | |
text | String | ✔️ | @db.Text | |
mediumtext | String | ✔️ | @db.MediumText | |
longtext | String | ✔️ | @db.LongText | |
set | Unsupported | 还没有 | ||
geometry | Unsupported | 还没有 | ||
point | Unsupported | 还没有 | ||
linestring | Unsupported | 还没有 | ||
polygon | Unsupported | 还没有 | ||
multipoint | Unsupported | 还没有 | ||
multilinestring | Unsupported | 还没有 | ||
multipolygon | Unsupported | 还没有 | ||
geometrycollection | Unsupported | 还没有 |
内省 添加了 Unsupported 字段尚不支持的原生数据库类型：
¥Introspection adds native database types that are not yet supported as Unsupported fields:
model Device {
id Int @id @default(autoincrement())
name String
data Unsupported("circle")
}
引擎
¥Engine
如果你使用的 MySQL 版本以 MyISAM 为默认引擎，则在创建表时必须指定 ENGINE = InnoDB;。如果你内省使用不同引擎的数据库，则不会创建 Prisma 模式中的关系（或者丢失，如果该关系已存在）。
¥If you are using a version of MySQL where MyISAM is the default engine, you must specify ENGINE = InnoDB; when you create a table. If you introspect a database that uses a different engine, relations in the Prisma Schema are not created (or lost, if the relation already existed).
权限
¥Permissions
全新安装的 MySQL/MariaDB 默认情况下只有 root 数据库用户。不要在 Prisma 配置中使用 root 用户，而是为每个应用创建一个数据库和数据库用户。在大多数 Linux 主机（例如 Ubuntu）上，你可以简单地以 Linux root 用户身份运行此命令（它也自动具有数据库 root 访问权限）：
¥A fresh new installation of MySQL/MariaDB has by default only a root database user. Do not use root user in your Prisma configuration, but instead create a database and database user for each application. On most Linux hosts (e.g. Ubuntu) you can simply run this as the Linux root user (which automatically has database root access as well):
mysql -e "CREATE DATABASE IF NOT EXISTS $DB_PRISMA;"
mysql -e "GRANT ALL PRIVILEGES ON $DB_PRISMA.* TO $DB_USER@'%' IDENTIFIED BY '$DB_PASSWORD';"
以上足以运行 prisma db pull 和 prisma db push 命令。为了也运行 prisma migrate 命令，需要授予这些权限：
¥The above is enough to run the prisma db pull and prisma db push commands. In order to also run prisma migrate commands these permissions need to be granted:
mysql -e "GRANT CREATE, DROP, REFERENCES, ALTER ON *.* TO $DB_USER@'%';"