I'm using the following technology stack
我正在使用以下技术堆栈
- Laravel 5.2
- Laravel 5.2
- MySQL
- MySQL的
and for security I'm using JWT (JSON Web Tokens)
为了安全起见，我正在使用JWT（JSON Web令牌）
I was able to secure my applications using JWT.
我能够使用JWT保护我的应用程序。
I would like to store JWT token in mysql database.
我想将JWT令牌存储在mysql数据库中。
QUESTION Which of the following data type is best to store JWT token in MySQL DB?
问题以下哪种数据类型最适合在MySQL DB中存储JWT令牌？
- VARCHAR
- VARCHAR
- CLOB
- CLOB
- TEXT
- 文本
- LONG TEXT
- 长篇文章
2 个解决方案
#1
6
As with anything else, the answer is "it depends".
与其他任何事情一样，答案是“它取决于”。
First, you need to determine if storing the fully encoded JWT is the correct solution. I tend to not store the JWT string and instead store the claims used to construct the JWT, which will save a ton of room in the database.
首先，您需要确定存储完全编码的JWT是否是正确的解决方案。我倾向于不存储JWT字符串，而是存储用于构建JWT的声明，这将在数据库中节省大量空间。
If you decide that storing the JWT is the correct method, then we can look at your options.
如果您认为存储JWT是正确的方法，那么我们可以查看您的选项。
TEXT and LONGTEXT are just types of CLOB, so we can ignore that one.
TEXT和LONGTEXT只是CLOB的类型，所以我们可以忽略它。
TEXT and VARCHAR both have limits of 64kb, so anything above that will require LONGTEXT (or MEDIUMTEXT, which you didn't mention but is an option).
TEXT和VARCHAR都有64kb的限制，所以高于此值的任何东西都需要LONGTEXT（或MEDIUMTEXT，你没有提到但是可以选择）。
The difference between TEXT and VARCHAR is that VARCHAR is stored in the row but TEXT is basically a pointer. VARCHAR will be faster if you are going to be reading the JWT often, but larger strings will cause each individual row to be larger, which will be a performance hit.
TEXT和VARCHAR之间的区别在于VARCHAR存储在行中，但TEXT基本上是指针。如果您要经常阅读JWT，VARCHAR会更快，但是更大的字符串将导致每个单独的行更大，这将是性能损失。
With as large as JWTs tend to be, I would say that TEXT is a pretty good choice to store JWTs in the database. If you are absolutely confident that the JWTs will stay very small, then a VARCHAR may produce better read performance, but you would would be best to test with real world data to be sure.
与JWT一样大，我会说TEXT是将JWT存储在数据库中的不错选择。如果你绝对相信JWT会保持很小，那么VARCHAR可能会产生更好的读取性能，但你最好用真实世界的数据进行测试。
If you need a field larger than TEXT is able to provide, then I would reiterate my recommendation to avoid storing the encoded JWT, but LONGTEXT is an option there.
如果你需要一个大于TEXT能够提供的字段，那么我会重申我的建议，以避免存储编码的JWT，但LONGTEXT是一个选项。
#2
1
Based on the example, I would suggest this for an 'encoded' base64 token:
根据这个例子，我建议使用'编码'的base64令牌：
TEXT CHARACTER SET ascii COLLATE ascii_bin
In general, JSON should be some size of TEXT or VARCHAR with CHARACTER SET utf8 or utf8mb4. (The COLLATION is likely to be irrelevant.)
通常，JSON应该是TEXT或VARCHAR的一些大小，CHARACTER SET为utf8或utf8mb4。 （COLLATION很可能无关紧要。）
TEXT is limited to 64KB; there is not much advantage in using a smaller VARCHAR.
TEXT限制为64KB;使用较小的VARCHAR没有太大的优势。
Re: "TEXT is just a pointer" -- Not quite correct. In some ROW_FORMATs in InnoDB, either TEXT or VARCHAR may be a pointer to an extension to the row. The action depends mostly on the ROW_FORMAT, not the datatype.
回复：“TEXT只是一个指针” - 不太正确。在InnoDB中的一些ROW_FORMAT中，TEXT或VARCHAR可以是指向该行扩展的指针。该操作主要取决于ROW_FORMAT，而不是数据类型。
#1
6
As with anything else, the answer is "it depends".
与其他任何事情一样，答案是“它取决于”。
First, you need to determine if storing the fully encoded JWT is the correct solution. I tend to not store the JWT string and instead store the claims used to construct the JWT, which will save a ton of room in the database.
首先，您需要确定存储完全编码的JWT是否是正确的解决方案。我倾向于不存储JWT字符串，而是存储用于构建JWT的声明，这将在数据库中节省大量空间。
If you decide that storing the JWT is the correct method, then we can look at your options.
如果您认为存储JWT是正确的方法，那么我们可以查看您的选项。
TEXT and LONGTEXT are just types of CLOB, so we can ignore that one.
TEXT和LONGTEXT只是CLOB的类型，所以我们可以忽略它。
TEXT and VARCHAR both have limits of 64kb, so anything above that will require LONGTEXT (or MEDIUMTEXT, which you didn't mention but is an option).
TEXT和VARCHAR都有64kb的限制，所以高于此值的任何东西都需要LONGTEXT（或MEDIUMTEXT，你没有提到但是可以选择）。
The difference between TEXT and VARCHAR is that VARCHAR is stored in the row but TEXT is basically a pointer. VARCHAR will be faster if you are going to be reading the JWT often, but larger strings will cause each individual row to be larger, which will be a performance hit.
TEXT和VARCHAR之间的区别在于VARCHAR存储在行中，但TEXT基本上是指针。如果您要经常阅读JWT，VARCHAR会更快，但是更大的字符串将导致每个单独的行更大，这将是性能损失。
With as large as JWTs tend to be, I would say that TEXT is a pretty good choice to store JWTs in the database. If you are absolutely confident that the JWTs will stay very small, then a VARCHAR may produce better read performance, but you would would be best to test with real world data to be sure.
与JWT一样大，我会说TEXT是将JWT存储在数据库中的不错选择。如果你绝对相信JWT会保持很小，那么VARCHAR可能会产生更好的读取性能，但你最好用真实世界的数据进行测试。
If you need a field larger than TEXT is able to provide, then I would reiterate my recommendation to avoid storing the encoded JWT, but LONGTEXT is an option there.
如果你需要一个大于TEXT能够提供的字段，那么我会重申我的建议，以避免存储编码的JWT，但LONGTEXT是一个选项。
#2
1
Based on the example, I would suggest this for an 'encoded' base64 token:
根据这个例子，我建议使用'编码'的base64令牌：
TEXT CHARACTER SET ascii COLLATE ascii_bin
In general, JSON should be some size of TEXT or VARCHAR with CHARACTER SET utf8 or utf8mb4. (The COLLATION is likely to be irrelevant.)
通常，JSON应该是TEXT或VARCHAR的一些大小，CHARACTER SET为utf8或utf8mb4。 （COLLATION很可能无关紧要。）
TEXT is limited to 64KB; there is not much advantage in using a smaller VARCHAR.
TEXT限制为64KB;使用较小的VARCHAR没有太大的优势。
Re: "TEXT is just a pointer" -- Not quite correct. In some ROW_FORMATs in InnoDB, either TEXT or VARCHAR may be a pointer to an extension to the row. The action depends mostly on the ROW_FORMAT, not the datatype.
回复：“TEXT只是一个指针” - 不太正确。在InnoDB中的一些ROW_FORMAT中，TEXT或VARCHAR可以是指向该行扩展的指针。该操作主要取决于ROW_FORMAT，而不是数据类型。