Design Data Structure SQL

First, we define a data table. The following is the SQL file of the data table to be used in this chapter’s example:

  1. CREATE TABLE `user` (
  2.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'user id',
  3.   `name` varchar(45) DEFAULT NULL COMMENT 'user name',
  4.   `status` tinyint DEFAULT NULL COMMENT 'user status',
  5.   `age` tinyint unsigned DEFAULT NULL COMMENT 'user age',
  6.   PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Apply Data Structure SQL

We need to apply this data table to the mysql database for subsequent use. If you don’t have a mysql database service locally, you can use docker to run one:

  1. docker run -d --name mysql \
  2.  -p 3306:3306 \
  3.  -e MYSQL_DATABASE=test \
  4.  -e MYSQL_ROOT_PASSWORD=12345678 \
  5.  loads/mysql:5.7

After starting, connect to the database and apply the table creation sql statements:

  1. $ mysql -h127.0.0.1 -p3306 -uroot -p
  2. mysql: [Warning] Using a password on the command line API can be insecure.
  3. Enter password: 
  4. Welcome to the MySQL monitor.  Commands end with ; or \g.
  5. Your MySQL connection id is 57
  6. Server version: 9.0.1 Homebrew
  8. Copyright (c) 2000, 2024, Oracle and/or its affiliates.
  10. Oracle is a registered trademark of Oracle Corporation and/or its
  11. affiliates. Other names may be trademarks of their respective
  12. owners.
  14. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  16. mysql> use test;
  17. Database changed
  18. mysql> CREATE TABLE `user` (
  19.     ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'user id',
  20.     ->   `name` varchar(45) DEFAULT NULL COMMENT 'user name',
  21.     ->   `status` tinyint DEFAULT NULL COMMENT 'user status',
  22.     ->   `age` tinyint unsigned DEFAULT NULL COMMENT 'user age',
  23.     ->   PRIMARY KEY (`id`)
  24.     -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  25. Query OK, 0 rows affected, 2 warnings (0.02 sec)
  27. mysql>

Learning Summary

It is a good development practice to design database tables before API development. Here we are using the mysql database, which requires setting up/running the database service first.

After designing the database table, we can use a scaffold tool to automatically generate corresponding database operation-related files in the next step.