The field types in MySQL?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
6 views
What types of would be ideologically correct for each field and why? Database started here only to study, so the question lamerskie in the future. Fields:
  • user_id
  • user_name
  • user_pass
  • user_invite
  • user_reg_flag
  • user_mail
  • user_course
by | 6 views

7 Answers

0 like 0 dislike
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;
by
0 like 0 dislike
Under name use 25 in the case of utf-8 is IMHO not the best idea, even if it is limited to Russian, the soap 50 also may not be enough in perverse cases, but the relevant rfc (domains.Russian for example :) ). Yes and no overhead between varchar(1) varchar(256) not, no reason to save not see if the requirements there are limits, test them in the application and on the number of characters, not bytes (for php mb_strlen () rather than strlen() ).
by
0 like 0 dislike
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;
by
0 like 0 dislike
Generally, if to think about a good architecture, it is not one table should be.
\r
Table1: ID, username, password
Table2: data about the user
The field value of the invite I did not understand, but if you mean "referral program" of some sort is also a separate table with data "user ID", "referrer"
and instead user_reg_flag to make a table of user roles in the system (you have, most likely, an administrator will be?) — first, the "login" and "admin" and link through linking the user table with roles that everyone could have some. Then upon confirmation of registration, for example, add user to role"login" if you want to ban (prevent login) — remove it.
\r
If interested, you can write the structure of these tables.
\r
PS: as for the subject — don't see the point in the optimization field without optimization of the database structure.
by
0 like 0 dislike
id int 10 unsigned
name — varchar 25
pass — varchar 32 (after md5)
email — varchar 50
\r
if you need a bool, tinyint 1
by
0 like 0 dislike
In sqlyog there is a wonderful analyzer tables. Create a table mindlessly fill it with data, then analyze and sqlyog offers you the optimal field types. From having to deal with the DBMS will not cure, but short term may solve your problem.
by
0 like 0 dislike
in the General case: use a fixed format (char instead of varchar) the minimum required length. You can also consider features of the architecture (32-bit server: ID — an unsigned integer of 32 bits).
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
3 answers
0 like 0 dislike
2 answers
asked Apr 3, 2019 by kvonosan
110,608 questions
257,186 answers
0 comments
28,033 users