{"id":2310,"date":"2011-03-03T08:27:58","date_gmt":"2011-03-02T22:27:58","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=2310"},"modified":"2011-03-03T08:27:58","modified_gmt":"2011-03-02T22:27:58","slug":"sql-oddity-alter-table-and-default-values","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/03\/sql-oddity-alter-table-and-default-values\/","title":{"rendered":"SQL Oddity: ALTER TABLE and default values"},"content":{"rendered":"

\u00ef\u00bb\u00bfSo, the MySQL (and Drizzle) ALTER TABLE syntax allows you to easily change the default value of a column. For example:<\/p>\n

CREATE TABLE t1 (answer int);\r\nALTER TABLE t1 ALTER answer SET DEFAULT 42;<\/pre>\n
So, you create a TIMESTAMP column and forgot to set the default value to CURRENT_TIMESTAMP. Easy, just ALTER TABLE:<\/p>\n
create table t1 (a timestamp);\r\nalter table t1 alter a set default CURRENT_TIMESTAMP;<\/pre>\n
(This is left as another exercise for the reader as to what this will do – again, maybe not what you expect)<\/p>\n","protected":false},"excerpt":{"rendered":"
\u00ef\u00bb\u00bfSo, the MySQL (and Drizzle) ALTER TABLE syntax allows you to easily change the default value of a column. For example: CREATE TABLE t1 (answer int); ALTER TABLE t1 ALTER answer SET DEFAULT 42; So, you create a TIMESTAMP column … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[75,14],"tags":[412,269,70,628],"class_list":["post-2310","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","category-mysql","tag-alter-table","tag-ddl","tag-drizzle","tag-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-Bg","jetpack-related-posts":[{"id":2307,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/01\/alter-table-rename-rename-rename\/","url_meta":{"origin":2310,"position":0},"title":"ALTER TABLE RENAME RENAME RENAME","author":"Stewart Smith","date":"2011-03-01","format":false,"excerpt":"Here's a nice challenge for you. What does the following do (or error out on?): CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); ALTER TABLE t1 RENAME t3, RENAME t2, RENAME t4; I'd be interested to know what a) you think it does and then b) if you\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2313,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/03\/undocumented-alter-table-that-does-nothing\/","url_meta":{"origin":2310,"position":1},"title":"Undocumented ALTER TABLE that does *nothing* (useful)","author":"Stewart Smith","date":"2011-03-03","format":false,"excerpt":"(at least since MySQL 5.1.42) alter table t1 force; Pretty neat huh? In fact, in Drizzle this will end up doing a copying alter table. Not useful. There's an over four year old bug report in MySQL (Bug#24091). I'm just going to remove that bit from the parser in Drizzle\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1576,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/02\/25\/no-implicit-defaults\/","url_meta":{"origin":2310,"position":2},"title":"No implicit defaults","author":"Stewart Smith","date":"2009-02-25","format":false,"excerpt":"See also: MySQL Bug 43151 The MySQL Manual proudly states that you don't get implicit default values if strict mode. mysql> set sql_mode='STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.00 sec) mysql> create table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1298,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/12\/11\/stewart-learns-more-sql-oddities-or-wtfs\/","url_meta":{"origin":2310,"position":3},"title":"Stewart learns more SQL oddities (or WTFs)","author":"Stewart Smith","date":"2008-12-11","format":false,"excerpt":"In what situation will this succeed: ALTER TABLE t1 RENAME t2; but this fail: RENAME TABLE t1 TO t2; ? Highlight the white text below for the answer: When t1 is a temporary table. Wow... I wonder why that is, I can't think of any good reason...","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":578,"url":"https:\/\/www.flamingspork.com\/blog\/2006\/02\/15\/information_schemafiles-querying-disk-usage-from-sql\/","url_meta":{"origin":2310,"position":4},"title":"INFORMATION_SCHEMA.FILES (querying disk usage from SQL)","author":"Stewart Smith","date":"2006-02-15","format":false,"excerpt":"In MySQL 5.1.6 there's a new INFORMATION_SCHEMA table. Currently, it only has information on files for NDB but we're hoping to change that in a future release (read: I think it would be neat). This table is a table generated by the MySQL server listing all the different files that\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1571,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/02\/24\/when-can-a-tinytext-column-have-a-default-value\/","url_meta":{"origin":2310,"position":5},"title":"When can a TINYTEXT column have a default value?","author":"Stewart Smith","date":"2009-02-24","format":false,"excerpt":"Well... kinda... (and nobody make fun of me for using my MythTV box as a testing ground for SQL against MySQL). myth@orpheus:~$ mysql -u root test Welcome to the MySQL monitor. Commands end with ; or \\g. Your MySQL connection id is 2496 Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu) Type 'help;' or\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2310","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=2310"}],"version-history":[{"count":2,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2310\/revisions"}],"predecessor-version":[{"id":2312,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2310\/revisions\/2312"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=2310"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=2310"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=2310"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}