What is the best MYSQL or Maria DB data type to store JWT token?

Question

I'm using the following technology stack

• Laravel 5.2
• MySQL

and for security I'm using JWT (JSON Web Tokens)

I was able to secure my applications using JWT.

I would like to store JWT token in mysql database.

QUESTION Which of the following data type is best to store JWT token in MySQL DB?

1. VARCHAR
2. CLOB
3. TEXT
4. LONG TEXT

Answer 1

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.

If you decide that storing the JWT is the correct method, then we can look at your options.

TEXT and LONGTEXT are just types of CLOB, so we can ignore that one.

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).

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.

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.

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.

Answer 2

Based on the example, I would suggest this for an 'encoded' base64 token:

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.)

TEXT is limited to 64KB; there is not much advantage in using a smaller 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.