Field types in MySQL?

Question

MySQL

What types would be ideologically correct for each of the fields and why? I just started to study databases, so the question is lamer, but necessary in the future. Fields:

• user_id
  
• username
  
• user_pass
  
• user_invite
  
• user_reg_flag
  
• user_mail
  
• user_course
  

Answer 1

CREATE TABLE `users` (
​​`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_name` char(50) NOT NULL DEFAULT '',
`user_pass` char(32) NOT NULL DEFAULT '',
`user_mail` char(50 ) NOT NULL DEFAULT '',
`user_invite` tinyint(1) NOT NULL DEFAULT 0,
`user_created` TIMESTAMP DEFAULT '0000-00-00 00:00:00' NOT NULL,
`user_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`user_id`),
UNIQUE KEY (`user_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Answer 2

Under name, using 25 in the case of utf-8 is not the best idea, even if you limit yourself to Russian, 50 may also not be enough for soap in perverted cases, but the corresponding rfc (domains.rf for example :)). And in general, there is no overhead between varchar(1) and varchar(256), I don’t see any point in saving, if there are restrictions in the requirements, then check them in the application and precisely for the number of characters, not bytes (for php - mb_strlen(), not strlen() ).

Answer 3

CREATE TABLE `users` (
​​`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL DEFAULT '',
`pass` varchar(32) NOT NULL DEFAULT '',
`mail` varchar(50 ) NOT NULL DEFAULT '',
`invite` tinyint(1) NOT NULL DEFAULT 0,
`created` TIMESTAMP DEFAULT '0000-00-00 00:00:00' NOT NULL,
`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Answer 4

In general, if you immediately think about good architecture, then this should not be one table.
Table1: id, login, password
Table2: user
data I didn’t understand the meaning of the invite field, but if you mean “referral program”, or its likeness, it’s also a separate table with the data “user id”, “referrer”
and instead of user_reg_flag do a table of user roles in the system (you still, most likely, will have an administrator?) - to begin with, “login” and “admin” and link them through a linking table of users with roles so that everyone can have several. Then, when confirming registration, for example, we add the “login” role to the user, if we want to ban (prohibit entry), we remove it.
If interested, I can write the structure of these tables.
PS: as for the subject itself, I don’t see the point in optimizing the fields without optimizing the database structure.

Answer 5

id - int 10 unsigned
name - varchar 25
pass - varchar 32 (after md5)
email - varchar 50
if you need a bool, then tinyint 1

Answer 6

sqlyog has a great table parser. You create a table mindlessly, fill it with data, then analyze it and sqlyog will suggest the optimal field types for you. It will not relieve you of the need to deal with the DBMS, but it can solve your problem in the short term.

Answer 7

in general: use a fixed format (char instead of varchar) of the minimum required length. You can also take into account the features of the architecture (32-bit server: id is an unsigned integer with a size of 32 bits).

Answer 8

I would say that habr is not a place for solving household problems.