Languages

MySQL - most common types used in real development - what are they?

6 points
Asked by:
Bartek
1205

I created this question to share the most common and helpful types in MySQL tables creation.
It can reduce the time we need to create new tables during development.

What are the most common MySQL types used during development?

1 answers
3 points
Answered by:
Bartek
1205

From java / php / cpp developer point of view:

CREATE DATABASE `test_database`;

You can execute this SQL create table, it was tested before sharing:

DROP TABLE IF EXISTS `most_common_mysql_types`;

CREATE TABLE `most_common_mysql_types` (

    -- long - auto increment id
    `user_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

     -- string max 255 size - we add INDEX at the end
    `username` VARCHAR(255) NOT NULL COLLATE 'utf8_bin',

     -- string max 255 size - we add unique index in the end
    `email` VARCHAR(255) NOT NULL COLLATE 'utf8_bin',

     -- date time - example: 2020-07-01 12:21:32
    `creation_time` DATETIME NOT NULL,

    -- int - saves space
    `user_role_id` INT(11) UNSIGNED NOT NULL,

    -- boolean 0/1 - true/false
    `disabled` TINYINT(4) NOT NULL DEFAULT 0,

    -- very long text
    `description` TEXT NOT NULL,

    -- bigint not auto incremented
    `total_points` BIGINT(20) UNSIGNED NOT NULL,

    -- int with default null example: NULL DEFAULT NULL
    `age` INT(11) NULL DEFAULT NULL,

	PRIMARY KEY (`user_id`),
	INDEX `username` (`username`),
	UNIQUE INDEX `email` (`email`),

	-- example of unique index on 2 fields
	UNIQUE INDEX `unique_id_email` (`user_id`, `email`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

Example insert:

INSERT INTO `most_common_mysql_types`
(`user_id`, `username`, `email`, `creation_time`, `user_role_id`, `disabled`, `description`, `total_points`, `age`)
VALUES (1, 'test', 'test@gmail.com', '2020-07-01 17:58:08', 1, 0, 'My description', 5, 25);

Result of insert, HeidiSQL screenshot on windows:

Links:

0 comments Add comment
Hey 👋
Would you like to know what we do?
  • Dirask is online IT community for professionals and hobbyist to share their knowledge and help each other in extraordinary easy way.
  • We welcome everyone
    no matter what the experience,
    no matter how basic the question is,
    this community will help you.