Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save DustyReagan/352508 to your computer and use it in GitHub Desktop.
Save DustyReagan/352508 to your computer and use it in GitHub Desktop.
Twitter User Object MySQL Schema
CREATE TABLE `user` (
`twitter_id` int(10) unsigned NOT NULL,
`created_at` timestamp NOT NULL default '0000-00-00 00:00:00',
`name` varchar(80) NOT NULL,
`screen_name` varchar(30) NOT NULL,
`location` varchar(120) default NULL,
`description` varchar(640) default NULL,
`profile_image_url` varchar(400) NOT NULL,
`url` varchar(100) default NULL,
`protected` tinyint(1) default '0',
`verified` tinyint(1) default '0',
`followers_count` int(10) unsigned NOT NULL,
`friends_count` int(10) unsigned NOT NULL,
`favourites_count` int(10) unsigned NOT NULL,
`statuses_count` int(10) unsigned default '0',
`profile_background_color` varchar(8) default NULL,
`profile_text_color` varchar(8) default NULL,
`profile_link_color` varchar(8) default NULL,
`profile_sidebar_fill_color` varchar(8) default NULL,
`profile_sidebar_border_color` varchar(8) default NULL,
`profile_background_image_url` varchar(400) default NULL,
`profile_background_tile` varchar(5) default NULL,
`utc_offset` int(10) default NULL,
`time_zone` varchar(120) default NULL,
`lang` char(2) default NULL,
`geo_enabled` tinyint(1) default '0',
`contributors_enabled` tinyint(1) default '0',
`status_id` bigint(20) unsigned default '0',
`status_created_at` timestamp NULL default NULL,
`status_text` varchar(560) default NULL,
`status_source` varchar(200) default NULL,
`status_truncated` tinyint(1) default NULL,
`status_in_reply_to_status_id` bigint(20) unsigned default NULL,
`status_in_reply_to_user_id` int(10) unsigned default NULL,
`status_favorited` tinyint(1) default NULL,
`status_in_reply_to_screen_name` varchar(120) default NULL,
PRIMARY KEY (`twitter_id`),
UNIQUE KEY `screen_name` (`screen_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@DustyReagan
Copy link
Author

Note that Twitter returns 0, 1, 'true', or 'false' for these fields:

  • 'protected'
  • 'verified'
  • 'geo_enabled'
  • 'contributors_enabled'
  • 'status_truncated'
  • 'status_favorited'

You must convert any 'true' or 'false' string returned from Twitter to 1, or 0 before saving to this table's tinyint(1) fields.

@DustyReagan
Copy link
Author

MySQL varchar lengths are calculated by multiplying the max length Twitter allows for the field * 4 to allow for potential 4 byte characters.

  • name = 20 * 4 = 80
  • location = 30 * 4 = 120
  • description = 160 * 4 = 640
  • url = 100 (This is Twitter's max length and only 1 byte characters are allowed in URLs)
  • status_text = 140 * 4 = 560

However, running max(length(FIELD_NAME)) on Friend Or Follow returns the following:

  • location = 370

@angelxmoreno
Copy link

language should allow for 'en-gb' so CHAR(6) seems more appropriate

@CornwallSEO
Copy link

twitter does not do en-gb just en

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment