Top community members
All Questions Ask Question

Have you ever heard about encyclopedia for code?

What about: Wiki for Code project?

* because I've tried to share code and my solutions on different services and it was removed or I was unwelcomed
   - maybe it wasn't good place, but this one is.

if you think you have solution that can help someone, you are welcome to share this solution - Click here

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

1 answers
6 points

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

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

Checkout latest Findings & News:

Checkout latest questions:

Checkout latest wiki articles:

Hey 👋
Would you like to know what we do?
  • Dirask is IT community, where we share coding knowledge and help each other to solve coding problems.
  • We welcome everyone,
    no matter what the experience,
    no matter how basic the question is,
    this community will help you.
Read more