Redis Storage SQL Table method

Original: Alca in Redis-land

Discovery Three: Where is my tables?

Using Redis is completely different from the SQL data tables we used before, there is no language to support you querying data on the server, there are only a few commands to help you manipulate the keys value in the database. The commands in Redis are data type sensitive, that is, you cannot execute a set command on a list, or you will get a hint of execution error. You can send commands to Redis server through REDIS-CLI or other interfaces in the programming language you are using. In the following example, we focus only on the command itself, not on which way you submit it to Redis server.

Imagine a simple SQL database table, like a table in some applications that holds user data:

ID Username password Name surname
1 user1 pass1 Bob Smith
2 User2 pass2 Mario Rossi

Storing data

If we wanted to store the above data in Redis, how would you design the database schema in Redis? It may be more intuitive to look at the app's vision. Using SQL, we obtain a user information in select by specifying a user ID, in other words, a way to differentiate between different data entities, so we can identify and obtain user information through a unique identity. So if the user ID information is added to the Redis key, then our query needs are resolved, and in Redis, the data is stored in the following form:

Key Value
User:1:username user1
User:1:password Pass1
User:1:name Bob
User:1:surname Smith
User:2:username User2
User:2:password Pass2
User:2:name Mario
User:2:surname Rossi

So, to be a user ID, we can read the user information in the form of key user:id:username,user:id:password,user:id:name,user:id:surname.

User Login

The above storage format can also be used for user logins, but requires a way to query the user's ID based on username. That means we also need to establish a connection between username and ID. This can be done by adding another Redis key "User:username:id".

Key value
User:user1:id 1
User:user2:id 2

Now if Mario Rossi wants to log in, we can get all the information from the user by first identifying the username through the key "User:user2:id".

Primary key

How is the uniqueness of the ID value guaranteed in Redis? In SQL, you can define the self-increment key by "ID int primary KEY auto_increment", and now we need a similar way to generate a different ID for each user. Based on the numeric data mentioned in the previously available data types, the scheme in Redis is to create a key "user:next_id" and use it as a counter, executing the INCR command on key "user:next_id" whenever a new user is added.

SELECT * from users;

The next challenge is to query the list of users. Perhaps you think the data store above us is enough to query the list of users: You can get the current value of "user:next_id" counter, and then walk through 0 to counter to get user data in one or more steps. But if a user is removed from the system (the deletion is described below), and we traverse all the IDs from 0 to counter, some IDs will not be queried for any data.

Although this is usually not a problem, we do not want to waste time on nonexistent user data, so we need to create another key "User:list" whose value is a list or set type that stores each new user ID and, if necessary, from "User:list" The ID is removed from the I prefer to use list because it can be paged through the Lrange command.

Delete User

Another problem to be faced with is "data integrity" and see what happens when we delete a user. We need to remove every reference to this user, that is to say, delete all of the following key "user:id:*", "User:username:id", and the user ID in "user:list".

Redis Storage SQL Table method