Debian Codegraph @

mysql-test/suite/storage_engine/col_opt_null.result

raw
1 #
2 # BINARY columns
3 #
4 DROP TABLE IF EXISTS t1;
5 CREATE TABLE t1 (b BINARY <CUSTOM_COL_OPTIONS> NULL,
6 b0 BINARY(0) <CUSTOM_COL_OPTIONS> NULL,
7 b1 BINARY(1) <CUSTOM_COL_OPTIONS> NULL,
8 b20 BINARY(20) <CUSTOM_COL_OPTIONS> NULL,
9 b255 BINARY(255) <CUSTOM_COL_OPTIONS> NULL
10 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
11 SHOW COLUMNS IN t1;
12 Field Type Null Key Default Extra
13 b binary(1) # # # #
14 b0 binary(0) # # # #
15 b1 binary(1) # # # #
16 b20 binary(20) # # # #
17 b255 binary(255) # # # #
18 INSERT INTO t1 (b,b0,b1,b20,b255) VALUES ('','','','','');
19 INSERT INTO t1 (b,b0,b1,b20,b255) VALUES ('a','','b','abcdefghi klmnopqrst', 'Creating an article for the Knowledgebase is similar to asking questions. First, navigate to the category where you feel the article should be. Once there, double check that an article doesn\'t already exist which would work.');
20 SELECT HEX(b), HEX(b0), HEX(b1), HEX(b20), HEX(b255) FROM t1;
21 HEX(b) HEX(b0) HEX(b1) HEX(b20) HEX(b255)
22 00 00 0000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
23 61 62 616263646566676869206B6C6D6E6F7071727374 4372656174696E6720616E2061727469636C6520666F7220746865204B6E6F776C65646765626173652069732073696D696C617220746F2061736B696E67207175657374696F6E732E2046697273742C206E6176696761746520746F207468652063617465676F727920776865726520796F75206665656C207468652061727469636C652073686F756C642062652E204F6E63652074686572652C20646F75626C6520636865636B207468617420616E2061727469636C6520646F65736E277420616C726561647920657869737420776869636820776F756C6420776F726B2E00000000000000000000000000000000000000000000000000000000000000
24 INSERT INTO t1 (b,b0,b1,b20,b255) VALUES ('abc', 'a', 'abc', REPEAT('a',21), REPEAT('x',256));
25 Warnings:
26 Warning 1265 Data truncated for column 'b' at row 1
27 Warning 1265 Data truncated for column 'b0' at row 1
28 Warning 1265 Data truncated for column 'b1' at row 1
29 Warning 1265 Data truncated for column 'b20' at row 1
30 Warning 1265 Data truncated for column 'b255' at row 1
31 INSERT INTO t1 (b,b0,b1,b20,b255) SELECT b255, b255, b255, b255, CONCAT(b255,b255) FROM t1;
32 Warnings:
33 Warning 1265 Data truncated for column 'b' at row 1
34 Warning 1265 Data truncated for column 'b0' at row 1
35 Warning 1265 Data truncated for column 'b1' at row 1
36 Warning 1265 Data truncated for column 'b20' at row 1
37 Warning 1265 Data truncated for column 'b255' at row 1
38 Warning 1265 Data truncated for column 'b' at row 2
39 Warning 1265 Data truncated for column 'b0' at row 2
40 Warning 1265 Data truncated for column 'b1' at row 2
41 Warning 1265 Data truncated for column 'b20' at row 2
42 Warning 1265 Data truncated for column 'b255' at row 2
43 Warning 1265 Data truncated for column 'b' at row 3
44 Warning 1265 Data truncated for column 'b0' at row 3
45 Warning 1265 Data truncated for column 'b1' at row 3
46 Warning 1265 Data truncated for column 'b20' at row 3
47 Warning 1265 Data truncated for column 'b255' at row 3
48 SELECT HEX(b), HEX(b0), HEX(b1), HEX(b20), HEX(b255) FROM t1;
49 HEX(b) HEX(b0) HEX(b1) HEX(b20) HEX(b255)
50 00 00 0000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
51 00 00 0000000000000000000000000000000000000000 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
52 43 43 4372656174696E6720616E2061727469636C6520 4372656174696E6720616E2061727469636C6520666F7220746865204B6E6F776C65646765626173652069732073696D696C617220746F2061736B696E67207175657374696F6E732E2046697273742C206E6176696761746520746F207468652063617465676F727920776865726520796F75206665656C207468652061727469636C652073686F756C642062652E204F6E63652074686572652C20646F75626C6520636865636B207468617420616E2061727469636C6520646F65736E277420616C726561647920657869737420776869636820776F756C6420776F726B2E00000000000000000000000000000000000000000000000000000000000000
53 61 61 6161616161616161616161616161616161616161 787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878
54 61 62 616263646566676869206B6C6D6E6F7071727374 4372656174696E6720616E2061727469636C6520666F7220746865204B6E6F776C65646765626173652069732073696D696C617220746F2061736B696E67207175657374696F6E732E2046697273742C206E6176696761746520746F207468652063617465676F727920776865726520796F75206665656C207468652061727469636C652073686F756C642062652E204F6E63652074686572652C20646F75626C6520636865636B207468617420616E2061727469636C6520646F65736E277420616C726561647920657869737420776869636820776F756C6420776F726B2E00000000000000000000000000000000000000000000000000000000000000
55 78 78 7878787878787878787878787878787878787878 787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878
56 ALTER TABLE t1 ADD COLUMN b257 BINARY(257) <CUSTOM_COL_OPTIONS> NULL;
57 ERROR 42000: Column length too big for column 'b257' (max = 255); use BLOB or TEXT instead
58 SHOW COLUMNS IN t1;
59 Field Type Null Key Default Extra
60 b binary(1) # # # #
61 b0 binary(0) # # # #
62 b1 binary(1) # # # #
63 b20 binary(20) # # # #
64 b255 binary(255) # # # #
65 DROP TABLE t1;
66 DROP TABLE IF EXISTS t1;
67 CREATE TABLE t1 (c BINARY <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
68 SHOW COLUMNS IN t1;
69 Field Type Null Key Default Extra
70 c binary(1) YES NULL
71 INSERT INTO t1 (c) VALUES (NULL);
72 SELECT COUNT(c), COUNT(*) FROM t1;
73 COUNT(c) COUNT(*)
74 0 1
75 DROP TABLE t1;
76 CREATE TABLE t1 (c BINARY <CUSTOM_COL_OPTIONS> NULL,
77 c1 BINARY <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
78 c2 BINARY <CUSTOM_COL_OPTIONS> NULL DEFAULT 0
79 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
80 SHOW COLUMNS IN t1;
81 Field Type Null Key Default Extra
82 c binary(1) YES NULL
83 c1 binary(1) YES NULL
84 c2 binary(1) YES 0
85 INSERT INTO t1 (c) VALUES (NULL);
86 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
87 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
88 1 0 0 1
89 DROP TABLE t1;
90 #
91 # VARBINARY columns
92 #
93 DROP TABLE IF EXISTS t1, t2;
94 CREATE TABLE t1 (v0 VARBINARY(0) <CUSTOM_COL_OPTIONS> NULL,
95 v1 VARBINARY(1) <CUSTOM_COL_OPTIONS> NULL,
96 v64 VARBINARY(64) <CUSTOM_COL_OPTIONS> NULL,
97 v65000 VARBINARY(65000) <CUSTOM_COL_OPTIONS> NULL
98 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
99 SHOW COLUMNS IN t1;
100 Field Type Null Key Default Extra
101 v0 varbinary(0) # # #
102 v1 varbinary(1) # # #
103 v64 varbinary(64) # # #
104 v65000 varbinary(65000) # # #
105 CREATE TABLE t2 (v VARBINARY(65532) <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
106 SHOW COLUMNS IN t2;
107 Field Type Null Key Default Extra
108 v varbinary(65532) # # #
109 INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('','','','');
110 INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('','y','Once there, double check that an article doesn\'t already exist','Here is a list of recommended books on MariaDB and MySQL. We\'ve provided links to Amazon.com here for convenience, but they can be found at many other bookstores, both online and off.
111
112 If you want to have your favorite MySQL / MariaDB book listed here, please leave a comment.
113 For developers who want to code on MariaDB or MySQL
114
115 * Understanding MySQL Internals by Sasha Pachev, former MySQL developer at MySQL AB.
116 o This is the only book we know about that describes the internals of MariaDB / MySQL. A must have for anyone who wants to understand and develop on MariaDB!
117 o Not all topics are covered and some parts are slightly outdated, but still the best book on this topic.
118 * MySQL 5.1 Plugin Development by Sergei Golubchik and Andrew Hutchings
119 o A must read for anyone wanting to write a plugin for MariaDB, written by the Sergei who designed the plugin interface for MySQL and MariaDB!
120
121 For MariaDB / MySQL end users
122
123 * MariaDB Crash Course by Ben Forta
124 o First MariaDB book!
125 o For people who want to learn SQL and the basics of MariaDB.
126 o Now shipping. Purchase at Amazon.com or your favorite bookseller.
127
128 * SQL-99 Complete, Really by Peter Gulutzan & Trudy Pelzer.
129 o Everything you wanted to know about the SQL 99 standard. Excellent reference book!
130 o Free to read in the Knowledgebase!
131
132 * MySQL (4th Edition) by Paul DuBois
133 o The \'default\' book to read if you wont to learn to use MySQL / MariaDB.
134
135 * MySQL Cookbook by Paul DuBois
136 o A lot of examples of how to use MySQL. As with all of Paul\'s books, it\'s worth its weight in gold and even enjoyable reading for such a \'dry\' subject.
137
138 * High Performance MySQL, Second Edition, By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek J. Balling, et al.
139 o \"High Performance MySQL is the definitive guide to building fast, reliable systems with MySQL. Written by noted experts with years of real-world experience building very large systems, this book covers every aspect of MySQL performance in detail, and focuses on robustness, security, and data integrity. Learn advanced techniques in depth so you can bring out MySQL\'s full power.\" (From the book description at O\'Reilly)
140
141 * MySQL Admin Cookbook
142 o A quick step-by-step guide for MySQL users and database administrators to tackle real-world challenges with MySQL configuration and administration
143
144 * MySQL 5.0 Certification Study Guide, By Paul DuBois, Stefan Hinz, Carsten Pedersen
145 o This is the official guide to cover the passing of the two MySQL Certification examinations. It is valid till version 5.0 of the server, so while it misses all the features available in MySQL 5.1 and greater (including MariaDB 5.1 and greater), it provides a good basic understanding of MySQL for the end-user. ');
146 SELECT HEX(v0), HEX(v1), HEX(v64), HEX(v65000) FROM t1;
147 HEX(v0) HEX(v1) HEX(v64) HEX(v65000)
148
149 79 4F6E63652074686572652C20646F75626C6520636865636B207468617420616E2061727469636C6520646F65736E277420616C7265616479206578697374 486572652069732061206C697374206F66207265636F6D6D656E64656420626F6F6B73206F6E204D61726961444220616E64204D7953514C2E2057652776652070726F7669646564206C696E6B7320746F20416D617A6F6E2E636F6D206865726520666F7220636F6E76656E69656E63652C2062757420746865792063616E20626520666F756E64206174206D616E79206F7468657220626F6F6B73746F7265732C20626F7468206F6E6C696E6520616E64206F66662E0A0A2020496620796F752077616E7420746F206861766520796F7572206661766F72697465204D7953514C202F204D61726961444220626F6F6B206C697374656420686572652C20706C65617365206C65617665206120636F6D6D656E742E0A2020466F7220646576656C6F706572732077686F2077616E7420746F20636F6465206F6E204D617269614442206F72204D7953514C0A0A2020202020202A20556E6465727374616E64696E67204D7953514C20496E7465726E616C73206279205361736861205061636865762C20666F726D6572204D7953514C20646576656C6F706572206174204D7953514C2041422E0A2020202020202020202020206F205468697320697320746865206F6E6C7920626F6F6B207765206B6E6F772061626F75742074686174206465736372696265732074686520696E7465726E616C73206F66204D617269614442202F204D7953514C2E2041206D757374206861766520666F7220616E796F6E652077686F2077616E747320746F20756E6465727374616E6420616E6420646576656C6F70206F6E204D617269614442210A2020202020202020202020206F204E6F7420616C6C20746F706963732061726520636F766572656420616E6420736F6D652070617274732061726520736C696768746C79206F757464617465642C20627574207374696C6C20746865206265737420626F6F6B206F6E207468697320746F7069632E200A2020202020202A204D7953514C20352E3120506C7567696E20446576656C6F706D656E742062792053657267656920476F6C75626368696B20616E6420416E64726577204875746368696E67730A2020202020202020202020206F2041206D757374207265616420666F7220616E796F6E652077616E74696E6720746F207772697465206120706C7567696E20666F72204D6172696144422C207772697474656E20627920746865205365726765692077686F2064657369676E65642074686520706C7567696E20696E7465726661636520666F72204D7953514C20616E64204D61726961444221200A0A2020466F72204D617269614442202F204D7953514C20656E642075736572730A0A2020202020202A204D61726961444220437261736820436F757273652062792042656E20466F7274610A2020202020202020202020206F204669727374204D61726961444220626F6F6B210A2020202020202020202020206F20466F722070656F706C652077686F2077616E7420746F206C6561726E2053514C20616E642074686520626173696373206F66204D6172696144422E0A2020202020202020202020206F204E6F77207368697070696E672E20507572636861736520617420416D617A6F6E2E636F6D206F7220796F7572206661766F7269746520626F6F6B73656C6C65722E200A0A2020202020202A2053514C2D393920436F6D706C6574652C205265616C6C792062792050657465722047756C75747A616E20262054727564792050656C7A65722E0A2020202020202020202020206F2045766572797468696E6720796F752077616E74656420746F206B6E6F772061626F7574207468652053514C203939207374616E646172642E20457863656C6C656E74207265666572656E636520626F6F6B210A2020202020202020202020206F204672656520746F207265616420696E20746865204B6E6F776C656467656261736521200A0A2020202020202A204D7953514C20283474682045646974696F6E29206279205061756C204475426F69730A2020202020202020202020206F20546865202764656661756C742720626F6F6B20746F207265616420696620796F7520776F6E7420746F206C6561726E20746F20757365204D7953514C202F204D6172696144422E200A0A2020202020202A204D7953514C20436F6F6B626F6F6B206279205061756C204475426F69730A2020202020202020202020206F2041206C6F74206F66206578616D706C6573206F6620686F7720746F20757365204D7953514C2E204173207769746820616C6C206F66205061756C277320626F6F6B732C206974277320776F727468206974732077656967687420696E20676F6C6420616E64206576656E20656E6A6F7961626C652072656164696E6720666F7220737563682061202764727927207375626A6563742E200A0A2020202020202A204869676820506572666F726D616E6365204D7953514C2C205365636F6E642045646974696F6E2C204279204261726F6E20536368776172747A2C205065746572205A6169747365762C20566164696D20546B616368656E6B6F2C204A6572656D7920442E205A61776F646E792C2041726A656E204C656E747A2C20446572656B204A2E2042616C6C696E672C20657420616C2E0A2020202020202020202020206F20224869676820506572666F726D616E6365204D7953514C2069732074686520646566696E697469766520677569646520746F206275696C64696E6720666173742C2072656C6961626C652073797374656D732077697468204D7953514C2E205772697474656E206279206E6F74656420657870657274732077697468207965617273206F66207265616C2D776F726C6420657870657269656E6365206275696C64696E672076657279206C617267652073797374656D732C207468697320626F6F6B20636F7665727320657665727920617370656374206F66204D7953514C20706572666F726D616E636520696E2064657461696C2C20616E6420666F6375736573206F6E20726F627573746E6573732C2073656375726974792C20616E64206461746120696E746567726974792E204C6561726E20616476616E63656420746563686E697175657320696E20646570746820736F20796F752063616E206272696E67206F7574204D7953514C27732066756C6C20706F7765722E22202846726F6D2074686520626F6F6B206465736372697074696F6E206174204F275265696C6C7929200A0A2020202020202A204D7953514C2041646D696E20436F6F6B626F6F6B0A2020202020202020202020206F204120717569636B20737465702D62792D7374657020677569646520666F72204D7953514C20757365727320616E642064617461626173652061646D696E6973747261746F727320746F207461636B6C65207265616C2D776F726C64206368616C6C656E6765732077697468204D7953514C20636F6E66696775726174696F6E20616E642061646D696E697374726174696F6E200A0A2020202020202A204D7953514C20352E302043657274696669636174696F6E2053747564792047756964652C204279205061756C204475426F69732C2053746566616E2048696E7A2C204361727374656E20506564657273656E0A2020202020202020202020206F205468697320697320746865206F6666696369616C20677569646520746F20636F766572207468652070617373696E67206F66207468652074776F204D7953514C2043657274696669636174696F6E206578616D696E6174696F6E732E2049742069732076616C69642074696C6C2076657273696F6E20352E30206F6620746865207365727665722C20736F207768696C65206974206D697373657320616C6C2074686520666561747572657320617661696C61626C6520696E204D7953514C20352E3120616E6420677265617465722028696E636C7564696E67204D61726961444220352E3120616E642067726561746572292C2069742070726F7669646573206120676F6F6420626173696320756E6465727374616E64696E67206F66204D7953514C20666F722074686520656E642D757365722E20
150 INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('y', 'yy', REPEAT('c',65), REPEAT('abcdefghi ',6501));
151 Warnings:
152 Warning 1265 Data truncated for column 'v0' at row 1
153 Warning 1265 Data truncated for column 'v1' at row 1
154 Warning 1265 Data truncated for column 'v64' at row 1
155 Warning 1265 Data truncated for column 'v65000' at row 1
156 INSERT INTO t1 (v0,v1,v64,v65000) SELECT v65000, v65000, v65000, CONCAT(v65000,v1) FROM t1;
157 Warnings:
158 Warning 1265 Data truncated for column 'v0' at row 2
159 Warning 1265 Data truncated for column 'v1' at row 2
160 Warning 1265 Data truncated for column 'v64' at row 2
161 Warning 1265 Data truncated for column 'v0' at row 3
162 Warning 1265 Data truncated for column 'v1' at row 3
163 Warning 1265 Data truncated for column 'v64' at row 3
164 Warning 1265 Data truncated for column 'v65000' at row 3
165 SELECT HEX(v0), HEX(v1), HEX(v64), LENGTH(HEX(v65000)) FROM t1;
166 HEX(v0) HEX(v1) HEX(v64) LENGTH(HEX(v65000))
167 0
168 0
169 48 486572652069732061206C697374206F66207265636F6D6D656E64656420626F6F6B73206F6E204D61726961444220616E64204D7953514C2E20576527766520 5932
170 61 61626364656667686920616263646566676869206162636465666768692061626364656667686920616263646566676869206162636465666768692061626364 130000
171 79 4F6E63652074686572652C20646F75626C6520636865636B207468617420616E2061727469636C6520646F65736E277420616C7265616479206578697374 5930
172 79 63636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363636363 130000
173 ALTER TABLE t1 ADD COLUMN v65536 VARBINARY(65536) <CUSTOM_COL_OPTIONS> NULL;
174 Warnings:
175 Note 1246 Converting column 'v65536' from VARBINARY to BLOB
176 SHOW COLUMNS IN t1;
177 Field Type Null Key Default Extra
178 v0 varbinary(0) # # #
179 v1 varbinary(1) # # #
180 v64 varbinary(64) # # #
181 v65000 varbinary(65000) # # #
182 v65536 mediumblob # # #
183 DROP TABLE t1, t2;
184 DROP TABLE IF EXISTS t1;
185 CREATE TABLE t1 (c VARBINARY(64) <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
186 SHOW COLUMNS IN t1;
187 Field Type Null Key Default Extra
188 c varbinary(64) YES NULL
189 INSERT INTO t1 (c) VALUES (NULL);
190 SELECT COUNT(c), COUNT(*) FROM t1;
191 COUNT(c) COUNT(*)
192 0 1
193 DROP TABLE t1;
194 CREATE TABLE t1 (c VARBINARY(64) <CUSTOM_COL_OPTIONS> NULL,
195 c1 VARBINARY(64) <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
196 c2 VARBINARY(64) <CUSTOM_COL_OPTIONS> NULL DEFAULT 'test'
197 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
198 SHOW COLUMNS IN t1;
199 Field Type Null Key Default Extra
200 c varbinary(64) YES NULL
201 c1 varbinary(64) YES NULL
202 c2 varbinary(64) YES test
203 INSERT INTO t1 (c) VALUES (NULL);
204 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
205 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
206 1 0 0 1
207 DROP TABLE t1;
208 #
209 # BIT columns
210 #
211 DROP TABLE IF EXISTS t1;
212 CREATE TABLE t1 (a BIT <CUSTOM_COL_OPTIONS> NULL,
213 b BIT(20) <CUSTOM_COL_OPTIONS> NULL,
214 c BIT(64) <CUSTOM_COL_OPTIONS> NULL,
215 d BIT(1) <CUSTOM_COL_OPTIONS> NULL
216 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
217 SHOW COLUMNS IN t1;
218 Field Type Null Key Default Extra
219 a bit(1) # # #
220 b bit(20) # # #
221 c bit(64) # # #
222 d bit(1) # # #
223 ALTER TABLE t1 DROP COLUMN d;
224 ALTER TABLE t1 ADD COLUMN d BIT(0) <CUSTOM_COL_OPTIONS> NULL;
225 SHOW COLUMNS IN t1;
226 Field Type Null Key Default Extra
227 a bit(1) # # #
228 b bit(20) # # #
229 c bit(64) # # #
230 d bit(1) # # #
231 INSERT INTO t1 (a,b,c,d) VALUES (0,POW(2,20)-1,b'1111111111111111111111111111111111111111111111111111111111111111',1);
232 SELECT BIN(a), HEX(b), c+0 FROM t1 WHERE d>0;
233 BIN(a) HEX(b) c+0
234 0 FFFFF 18446744073709551615
235 INSERT INTO t1 (a,b,c,d) VALUES (1,0,-1,0);
236 SELECT a+0, b+0, c+0 FROM t1 WHERE d<100;
237 a+0 b+0 c+0
238 0 1048575 18446744073709551615
239 1 0 18446744073709551615
240 INSERT INTO t1 (a,b,c,d) VALUES (b'1', 'f', 0xFF, 0x0);
241 SELECT a+0, b+0, c+0 FROM t1 WHERE d IN (0, 2);
242 a+0 b+0 c+0
243 1 0 18446744073709551615
244 1 102 255
245 INSERT INTO t1 (a,b,c,d) VALUES (0x10,0,0,1);
246 Warnings:
247 Warning 1264 Out of range value for column 'a' at row 1
248 SELECT a,b,c,d FROM t1;
249 a b c d
250 INSERT INTO t1 (a,b,c,d) VALUES (0x01,0,0x10000000000000000,0);
251 Warnings:
252 Warning 1264 Out of range value for column 'c' at row 1
253 SELECT a,b,c,d FROM t1;
254 a b c d
255 DROP TABLE t1;
256 CREATE TABLE t1 (a BIT(65) <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
257 ERROR 42000: Display width out of range for 'a' (max = 64)
258 DROP TABLE IF EXISTS t1;
259 CREATE TABLE t1 (c BIT <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
260 SHOW COLUMNS IN t1;
261 Field Type Null Key Default Extra
262 c bit(1) YES NULL
263 INSERT INTO t1 (c) VALUES (NULL);
264 SELECT COUNT(c), COUNT(*) FROM t1;
265 COUNT(c) COUNT(*)
266 0 1
267 DROP TABLE t1;
268 CREATE TABLE t1 (c BIT <CUSTOM_COL_OPTIONS> NULL,
269 c1 BIT <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
270 c2 BIT <CUSTOM_COL_OPTIONS> NULL DEFAULT 1
271 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
272 SHOW COLUMNS IN t1;
273 Field Type Null Key Default Extra
274 c bit(1) YES NULL
275 c1 bit(1) YES NULL
276 c2 bit(1) YES b'1'
277 INSERT INTO t1 (c) VALUES (NULL);
278 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
279 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
280 1 0 0 1
281 DROP TABLE t1;
282 #
283 # BLOB columns
284 #
285 DROP TABLE IF EXISTS t1;
286 CREATE TABLE t1 (b BLOB <CUSTOM_COL_OPTIONS> NULL,
287 b0 BLOB(0) <CUSTOM_COL_OPTIONS> NULL,
288 b1 BLOB(1) <CUSTOM_COL_OPTIONS> NULL,
289 b300 BLOB(300) <CUSTOM_COL_OPTIONS> NULL,
290 bm BLOB(65535) <CUSTOM_COL_OPTIONS> NULL,
291 b70k BLOB(70000) <CUSTOM_COL_OPTIONS> NULL,
292 b17m BLOB(17000000) <CUSTOM_COL_OPTIONS> NULL,
293 t TINYBLOB <CUSTOM_COL_OPTIONS> NULL,
294 m MEDIUMBLOB <CUSTOM_COL_OPTIONS> NULL,
295 l LONGBLOB <CUSTOM_COL_OPTIONS> NULL
296 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
297 SHOW COLUMNS IN t1;
298 Field Type Null Key Default Extra
299 b blob # # #
300 b0 blob # # #
301 b1 tinyblob # # #
302 b300 blob # # #
303 bm blob # # #
304 b70k mediumblob # # #
305 b17m longblob # # #
306 t tinyblob # # #
307 m mediumblob # # #
308 l longblob # # #
309 INSERT INTO t1 (b,b0,b1,b300,bm,b70k,b17m,t,m,l) VALUES
310 ('','','','','','','','','',''),
311 ('a','b','c','d','e','f','g','h','i','j'),
312 ('test1','test2','test3','test4','test5','test6','test7','test8','test9','test10'),
313 ( REPEAT('a',65535), REPEAT('b',65535), REPEAT('c',255), REPEAT('d',65535), REPEAT('e',65535), REPEAT('f',1048576), HEX(REPEAT('g',1048576)), REPEAT('h',255), REPEAT('i',1048576), HEX(REPEAT('j',1048576)) );
314 SELECT LENGTH(b), LENGTH(b0), LENGTH(b1), LENGTH(b300), LENGTH(bm), LENGTH(b70k), LENGTH(b17m), LENGTH(t), LENGTH(m), LENGTH(l) FROM t1;
315 LENGTH(b) LENGTH(b0) LENGTH(b1) LENGTH(b300) LENGTH(bm) LENGTH(b70k) LENGTH(b17m) LENGTH(t) LENGTH(m) LENGTH(l)
316 0 0 0 0 0 0 0 0 0 0
317 1 1 1 1 1 1 1 1 1 1
318 5 5 5 5 5 5 5 5 5 6
319 65535 65535 255 65535 65535 1048576 2097152 255 1048576 2097152
320 INSERT INTO t1 (b,b0,b1,b300,bm,b70k,b17m,t,m,l) VALUES
321 ( REPEAT('a',65536), REPEAT('b',65536), REPEAT('c',256), REPEAT('d',65536), REPEAT('e',65536), REPEAT('f',1048576), REPEAT('g',1048576), REPEAT('h',256), REPEAT('i',1048576), REPEAT('j',1048576) );
322 Warnings:
323 Warning 1265 Data truncated for column 'b' at row 1
324 Warning 1265 Data truncated for column 'b0' at row 1
325 Warning 1265 Data truncated for column 'b1' at row 1
326 Warning 1265 Data truncated for column 'b300' at row 1
327 Warning 1265 Data truncated for column 'bm' at row 1
328 Warning 1265 Data truncated for column 't' at row 1
329 SELECT LENGTH(b), LENGTH(b0), LENGTH(b1), LENGTH(b300), LENGTH(bm), LENGTH(b70k), LENGTH(b17m), LENGTH(t), LENGTH(m), LENGTH(l) FROM t1;
330 LENGTH(b) LENGTH(b0) LENGTH(b1) LENGTH(b300) LENGTH(bm) LENGTH(b70k) LENGTH(b17m) LENGTH(t) LENGTH(m) LENGTH(l)
331 0 0 0 0 0 0 0 0 0 0
332 1 1 1 1 1 1 1 1 1 1
333 5 5 5 5 5 5 5 5 5 6
334 65535 65535 255 65535 65535 1048576 1048576 255 1048576 1048576
335 65535 65535 255 65535 65535 1048576 2097152 255 1048576 2097152
336 ALTER TABLE t1 ADD COLUMN bbb BLOB(4294967296);
337 ERROR 42000: Display width out of range for 'bbb' (max = 4294967295)
338 DROP TABLE t1;
339 DROP TABLE IF EXISTS t1;
340 CREATE TABLE t1 (c BLOB <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
341 SHOW COLUMNS IN t1;
342 Field Type Null Key Default Extra
343 c blob YES NULL
344 INSERT INTO t1 (c) VALUES (NULL);
345 SELECT COUNT(c), COUNT(*) FROM t1;
346 COUNT(c) COUNT(*)
347 0 1
348 DROP TABLE t1;
349 DROP TABLE IF EXISTS t1;
350 CREATE TABLE t1 (c TINYBLOB <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
351 SHOW COLUMNS IN t1;
352 Field Type Null Key Default Extra
353 c tinyblob YES NULL
354 INSERT INTO t1 (c) VALUES (NULL);
355 SELECT COUNT(c), COUNT(*) FROM t1;
356 COUNT(c) COUNT(*)
357 0 1
358 DROP TABLE t1;
359 DROP TABLE IF EXISTS t1;
360 CREATE TABLE t1 (c MEDIUMBLOB <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
361 SHOW COLUMNS IN t1;
362 Field Type Null Key Default Extra
363 c mediumblob YES NULL
364 INSERT INTO t1 (c) VALUES (NULL);
365 SELECT COUNT(c), COUNT(*) FROM t1;
366 COUNT(c) COUNT(*)
367 0 1
368 DROP TABLE t1;
369 DROP TABLE IF EXISTS t1;
370 CREATE TABLE t1 (c LONGBLOB <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
371 SHOW COLUMNS IN t1;
372 Field Type Null Key Default Extra
373 c longblob YES NULL
374 INSERT INTO t1 (c) VALUES (NULL);
375 SELECT COUNT(c), COUNT(*) FROM t1;
376 COUNT(c) COUNT(*)
377 0 1
378 DROP TABLE t1;
379 #
380 # BOOL columns
381 #
382 DROP TABLE IF EXISTS t1;
383 CREATE TABLE t1 (b1 BOOL <CUSTOM_COL_OPTIONS> NULL,
384 b2 BOOLEAN <CUSTOM_COL_OPTIONS> NULL
385 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
386 SHOW COLUMNS IN t1;
387 Field Type Null Key Default Extra
388 b1 tinyint(1) # # #
389 b2 tinyint(1) # # #
390 INSERT INTO t1 (b1,b2) VALUES (1,TRUE);
391 SELECT b1,b2 FROM t1;
392 b1 b2
393 1 1
394 INSERT INTO t1 (b1,b2) VALUES (FALSE,0);
395 SELECT b1,b2 FROM t1;
396 b1 b2
397 0 0
398 1 1
399 INSERT INTO t1 (b1,b2) VALUES (2,3);
400 SELECT b1,b2 FROM t1;
401 b1 b2
402 0 0
403 1 1
404 2 3
405 INSERT INTO t1 (b1,b2) VALUES (-1,-2);
406 SELECT b1,b2 FROM t1;
407 b1 b2
408 -1 -2
409 0 0
410 1 1
411 2 3
412 SELECT IF(b1,'true','false') AS a, IF(b2,'true','false') AS b FROM t1;
413 a b
414 false false
415 true true
416 true true
417 true true
418 SELECT b1,b2 FROM t1 WHERE b1 = TRUE;
419 b1 b2
420 1 1
421 SELECT b1,b2 FROM t1 WHERE b2 = FALSE;
422 b1 b2
423 0 0
424 INSERT INTO t1 (b1,b2) VALUES ('a','b');
425 Warnings:
426 Warning 1366 Incorrect integer value: 'a' for column 'b1' at row 1
427 Warning 1366 Incorrect integer value: 'b' for column 'b2' at row 1
428 SELECT b1,b2 FROM t1;
429 b1 b2
430 -1 -2
431 0 0
432 0 0
433 1 1
434 2 3
435 INSERT INTO t1 (b1,b2) VALUES (128,-129);
436 Warnings:
437 Warning 1264 Out of range value for column 'b1' at row 1
438 Warning 1264 Out of range value for column 'b2' at row 1
439 SELECT b1,b2 FROM t1;
440 b1 b2
441 -1 -2
442 0 0
443 0 0
444 1 1
445 127 -128
446 2 3
447 ALTER TABLE t1 ADD COLUMN b3 BOOLEAN UNSIGNED;
448 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNSIGNED' at line 1
449 ALTER TABLE t1 ADD COLUMN b3 BOOL ZEROFILL;
450 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ZEROFILL' at line 1
451 DROP TABLE t1;
452 DROP TABLE IF EXISTS t1;
453 CREATE TABLE t1 (c BOOL <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
454 SHOW COLUMNS IN t1;
455 Field Type Null Key Default Extra
456 c tinyint(1) YES NULL
457 INSERT INTO t1 (c) VALUES (NULL);
458 SELECT COUNT(c), COUNT(*) FROM t1;
459 COUNT(c) COUNT(*)
460 0 1
461 DROP TABLE t1;
462 CREATE TABLE t1 (c BOOL <CUSTOM_COL_OPTIONS> NULL,
463 c1 BOOL <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
464 c2 BOOL <CUSTOM_COL_OPTIONS> NULL DEFAULT '0'
465 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
466 SHOW COLUMNS IN t1;
467 Field Type Null Key Default Extra
468 c tinyint(1) YES NULL
469 c1 tinyint(1) YES NULL
470 c2 tinyint(1) YES 0
471 INSERT INTO t1 (c) VALUES (NULL);
472 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
473 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
474 1 0 0 1
475 DROP TABLE t1;
476 #
477 # CHAR columns
478 #
479 DROP TABLE IF EXISTS t1;
480 CREATE TABLE t1 (c CHAR <CUSTOM_COL_OPTIONS> NULL,
481 c0 CHAR(0) <CUSTOM_COL_OPTIONS> NULL,
482 c1 CHAR(1) <CUSTOM_COL_OPTIONS> NULL,
483 c20 CHAR(20) <CUSTOM_COL_OPTIONS> NULL,
484 c255 CHAR(255) <CUSTOM_COL_OPTIONS> NULL
485 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
486 SHOW COLUMNS IN t1;
487 Field Type Null Key Default Extra
488 c char(1) # # #
489 c0 char(0) # # #
490 c1 char(1) # # #
491 c20 char(20) # # #
492 c255 char(255) # # #
493 INSERT INTO t1 (c,c0,c1,c20,c255) VALUES ('','','','','');
494 INSERT INTO t1 (c,c0,c1,c20,c255) VALUES ('a','','b','abcdefghi klmnopqrst', 'Creating an article for the Knowledgebase is similar to asking questions. First, navigate to the category where you feel the article should be. Once there, double check that an article doesn\'t already exist which would work.');
495 SELECT c,c0,c1,c20,c255 FROM t1;
496 c c0 c1 c20 c255
497
498 a b abcdefghi klmnopqrst Creating an article for the Knowledgebase is similar to asking questions. First, navigate to the category where you feel the article should be. Once there, double check that an article doesn't already exist which would work.
499 INSERT INTO t1 (c,c0,c1,c20,c255) VALUES ('abc', 'a', 'abc', REPEAT('a',21), REPEAT('x',256));
500 Warnings:
501 Warning 1265 Data truncated for column 'c' at row 1
502 Warning 1265 Data truncated for column 'c0' at row 1
503 Warning 1265 Data truncated for column 'c1' at row 1
504 Warning 1265 Data truncated for column 'c20' at row 1
505 Warning 1265 Data truncated for column 'c255' at row 1
506 INSERT INTO t1 (c,c0,c1,c20,c255) SELECT c255, c255, c255, c255, CONCAT(c255,c1) FROM t1;
507 Warnings:
508 Warning 1265 Data truncated for column 'c' at row 2
509 Warning 1265 Data truncated for column 'c0' at row 2
510 Warning 1265 Data truncated for column 'c1' at row 2
511 Warning 1265 Data truncated for column 'c20' at row 2
512 Warning 1265 Data truncated for column 'c' at row 3
513 Warning 1265 Data truncated for column 'c0' at row 3
514 Warning 1265 Data truncated for column 'c1' at row 3
515 Warning 1265 Data truncated for column 'c20' at row 3
516 Warning 1265 Data truncated for column 'c255' at row 3
517 SELECT c,c0,c1,c20,c255 FROM t1;
518 c c0 c1 c20 c255
519
520
521 C C Creating an article Creating an article for the Knowledgebase is similar to asking questions. First, navigate to the category where you feel the article should be. Once there, double check that an article doesn't already exist which would work.b
522 a a aaaaaaaaaaaaaaaaaaaa xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
523 a b abcdefghi klmnopqrst Creating an article for the Knowledgebase is similar to asking questions. First, navigate to the category where you feel the article should be. Once there, double check that an article doesn't already exist which would work.
524 x x xxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
525 SELECT DISTINCT c20, REPEAT('a',LENGTH(c20)), COUNT(*) FROM t1 GROUP BY c1, c20;
526 c20 REPEAT('a',LENGTH(c20)) COUNT(*)
527 2
528 Creating an article aaaaaaaaaaaaaaaaaaa 1
529 aaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaa 1
530 abcdefghi klmnopqrst aaaaaaaaaaaaaaaaaaaa 1
531 xxxxxxxxxxxxxxxxxxxx aaaaaaaaaaaaaaaaaaaa 1
532 ALTER TABLE t1 ADD COLUMN c257 CHAR(257) <CUSTOM_COL_OPTIONS> NULL;
533 ERROR 42000: Column length too big for column 'c257' (max = 255); use BLOB or TEXT instead
534 DROP TABLE t1;
535 DROP TABLE IF EXISTS t1;
536 CREATE TABLE t1 (c CHAR <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
537 SHOW COLUMNS IN t1;
538 Field Type Null Key Default Extra
539 c char(1) YES NULL
540 INSERT INTO t1 (c) VALUES (NULL);
541 SELECT COUNT(c), COUNT(*) FROM t1;
542 COUNT(c) COUNT(*)
543 0 1
544 DROP TABLE t1;
545 CREATE TABLE t1 (c CHAR <CUSTOM_COL_OPTIONS> NULL,
546 c1 CHAR <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
547 c2 CHAR <CUSTOM_COL_OPTIONS> NULL DEFAULT '_'
548 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
549 SHOW COLUMNS IN t1;
550 Field Type Null Key Default Extra
551 c char(1) YES NULL
552 c1 char(1) YES NULL
553 c2 char(1) YES _
554 INSERT INTO t1 (c) VALUES (NULL);
555 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
556 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
557 1 0 0 1
558 DROP TABLE t1;
559 #
560 # VARCHAR columns
561 #
562 DROP TABLE IF EXISTS t1, t2;
563 CREATE TABLE t1 (v0 VARCHAR(0) <CUSTOM_COL_OPTIONS> NULL,
564 v1 VARCHAR(1) <CUSTOM_COL_OPTIONS> NULL,
565 v64 VARCHAR(64) <CUSTOM_COL_OPTIONS> NULL,
566 v65000 VARCHAR(65000) <CUSTOM_COL_OPTIONS> NULL
567 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
568 SHOW COLUMNS IN t1;
569 Field Type Null Key Default Extra
570 v0 varchar(0) # # #
571 v1 varchar(1) # # #
572 v64 varchar(64) # # #
573 v65000 varchar(65000) # # #
574 CREATE TABLE t2 (v VARCHAR(65532) <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
575 SHOW COLUMNS IN t2;
576 Field Type Null Key Default Extra
577 v varchar(65532) # # #
578 INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('','','','');
579 INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('','y','Once there, double check that an article doesn\'t already exist','Here is a list of recommended books on MariaDB and MySQL. We\'ve provided links to Amazon.com here for convenience, but they can be found at many other bookstores, both online and off.
580
581 If you want to have your favorite MySQL / MariaDB book listed here, please leave a comment.
582 For developers who want to code on MariaDB or MySQL
583
584 * Understanding MySQL Internals by Sasha Pachev, former MySQL developer at MySQL AB.
585 o This is the only book we know about that describes the internals of MariaDB / MySQL. A must have for anyone who wants to understand and develop on MariaDB!
586 o Not all topics are covered and some parts are slightly outdated, but still the best book on this topic.
587 * MySQL 5.1 Plugin Development by Sergei Golubchik and Andrew Hutchings
588 o A must read for anyone wanting to write a plugin for MariaDB, written by the Sergei who designed the plugin interface for MySQL and MariaDB!
589
590 For MariaDB / MySQL end users
591
592 * MariaDB Crash Course by Ben Forta
593 o First MariaDB book!
594 o For people who want to learn SQL and the basics of MariaDB.
595 o Now shipping. Purchase at Amazon.com or your favorite bookseller.
596
597 * SQL-99 Complete, Really by Peter Gulutzan & Trudy Pelzer.
598 o Everything you wanted to know about the SQL 99 standard. Excellent reference book!
599 o Free to read in the Knowledgebase!
600
601 * MySQL (4th Edition) by Paul DuBois
602 o The \'default\' book to read if you wont to learn to use MySQL / MariaDB.
603
604 * MySQL Cookbook by Paul DuBois
605 o A lot of examples of how to use MySQL. As with all of Paul\'s books, it\'s worth its weight in gold and even enjoyable reading for such a \'dry\' subject.
606
607 * High Performance MySQL, Second Edition, By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek J. Balling, et al.
608 o \"High Performance MySQL is the definitive guide to building fast, reliable systems with MySQL. Written by noted experts with years of real-world experience building very large systems, this book covers every aspect of MySQL performance in detail, and focuses on robustness, security, and data integrity. Learn advanced techniques in depth so you can bring out MySQL\'s full power.\" (From the book description at O\'Reilly)
609
610 * MySQL Admin Cookbook
611 o A quick step-by-step guide for MySQL users and database administrators to tackle real-world challenges with MySQL configuration and administration
612
613 * MySQL 5.0 Certification Study Guide, By Paul DuBois, Stefan Hinz, Carsten Pedersen
614 o This is the official guide to cover the passing of the two MySQL Certification examinations. It is valid till version 5.0 of the server, so while it misses all the features available in MySQL 5.1 and greater (including MariaDB 5.1 and greater), it provides a good basic understanding of MySQL for the end-user. ');
615 SELECT v0,v1,v64,v65000 FROM t1;
616 v0 v1 v64 v65000
617
618
619
620
621
622
623
624
625
626
627
628 y Once there, double check that an article doesn't already exist Here is a list of recommended books on MariaDB and MySQL. We've provided links to Amazon.com here for convenience, but they can be found at many other bookstores, both online and off.
629 o "High Performance MySQL is the definitive guide to building fast, reliable systems with MySQL. Written by noted experts with years of real-world experience building very large systems, this book covers every aspect of MySQL performance in detail, and focuses on robustness, security, and data integrity. Learn advanced techniques in depth so you can bring out MySQL's full power." (From the book description at O'Reilly)
630 o A lot of examples of how to use MySQL. As with all of Paul's books, it's worth its weight in gold and even enjoyable reading for such a 'dry' subject.
631 o A must read for anyone wanting to write a plugin for MariaDB, written by the Sergei who designed the plugin interface for MySQL and MariaDB!
632 o A quick step-by-step guide for MySQL users and database administrators to tackle real-world challenges with MySQL configuration and administration
633 o Everything you wanted to know about the SQL 99 standard. Excellent reference book!
634 o First MariaDB book!
635 o For people who want to learn SQL and the basics of MariaDB.
636 o Free to read in the Knowledgebase!
637 o Not all topics are covered and some parts are slightly outdated, but still the best book on this topic.
638 o Now shipping. Purchase at Amazon.com or your favorite bookseller.
639 o The 'default' book to read if you wont to learn to use MySQL / MariaDB.
640 o This is the official guide to cover the passing of the two MySQL Certification examinations. It is valid till version 5.0 of the server, so while it misses all the features available in MySQL 5.1 and greater (including MariaDB 5.1 and greater), it provides a good basic understanding of MySQL for the end-user.
641 o This is the only book we know about that describes the internals of MariaDB / MySQL. A must have for anyone who wants to understand and develop on MariaDB!
642 * High Performance MySQL, Second Edition, By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek J. Balling, et al.
643 * MariaDB Crash Course by Ben Forta
644 * MySQL (4th Edition) by Paul DuBois
645 * MySQL 5.0 Certification Study Guide, By Paul DuBois, Stefan Hinz, Carsten Pedersen
646 * MySQL 5.1 Plugin Development by Sergei Golubchik and Andrew Hutchings
647 * MySQL Admin Cookbook
648 * MySQL Cookbook by Paul DuBois
649 * SQL-99 Complete, Really by Peter Gulutzan & Trudy Pelzer.
650 * Understanding MySQL Internals by Sasha Pachev, former MySQL developer at MySQL AB.
651 For MariaDB / MySQL end users
652 For developers who want to code on MariaDB or MySQL
653 If you want to have your favorite MySQL / MariaDB book listed here, please leave a comment.
654 INSERT INTO t1 (v0,v1,v64,v65000) VALUES ('y', 'yy', REPEAT('c',65), REPEAT('abcdefghi ',6501));
655 Warnings:
656 Warning 1265 Data truncated for column 'v0' at row 1
657 Warning 1265 Data truncated for column 'v1' at row 1
658 Warning 1265 Data truncated for column 'v64' at row 1
659 Warning 1265 Data truncated for column 'v65000' at row 1
660 INSERT INTO t1 (v0,v1,v64,v65000) SELECT v65000, v65000, v65000, CONCAT(v65000,v1) FROM t1;
661 Warnings:
662 Warning 1265 Data truncated for column 'v0' at row 2
663 Warning 1265 Data truncated for column 'v1' at row 2
664 Warning 1265 Data truncated for column 'v64' at row 2
665 Warning 1265 Data truncated for column 'v0' at row 3
666 Warning 1265 Data truncated for column 'v1' at row 3
667 Warning 1265 Data truncated for column 'v64' at row 3
668 Warning 1265 Data truncated for column 'v65000' at row 3
669 SELECT v0, v1, v64, LENGTH(v65000) FROM t1;
670 v0 v1 v64 LENGTH(v65000)
671 0
672 0
673 H Here is a list of recommended books on MariaDB and MySQL. We've 2966
674 a abcdefghi abcdefghi abcdefghi abcdefghi abcdefghi abcdefghi abcd 65000
675 y Once there, double check that an article doesn't already exist 2965
676 y cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc 65000
677 ALTER TABLE t1 ADD COLUMN v65536 VARCHAR(65536) <CUSTOM_COL_OPTIONS> NULL;
678 Warnings:
679 Note 1246 Converting column 'v65536' from VARCHAR to TEXT
680 SHOW COLUMNS IN t1;
681 Field Type Null Key Default Extra
682 v0 varchar(0) # # #
683 v1 varchar(1) # # #
684 v64 varchar(64) # # #
685 v65000 varchar(65000) # # #
686 v65536 mediumtext # # #
687 DROP TABLE t1, t2;
688 DROP TABLE IF EXISTS t1;
689 CREATE TABLE t1 (c VARCHAR(64) <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
690 SHOW COLUMNS IN t1;
691 Field Type Null Key Default Extra
692 c varchar(64) YES NULL
693 INSERT INTO t1 (c) VALUES (NULL);
694 SELECT COUNT(c), COUNT(*) FROM t1;
695 COUNT(c) COUNT(*)
696 0 1
697 DROP TABLE t1;
698 CREATE TABLE t1 (c VARCHAR(64) <CUSTOM_COL_OPTIONS> NULL,
699 c1 VARCHAR(64) <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
700 c2 VARCHAR(64) <CUSTOM_COL_OPTIONS> NULL DEFAULT 'test default'
701 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
702 SHOW COLUMNS IN t1;
703 Field Type Null Key Default Extra
704 c varchar(64) YES NULL
705 c1 varchar(64) YES NULL
706 c2 varchar(64) YES test default
707 INSERT INTO t1 (c) VALUES (NULL);
708 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
709 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
710 1 0 0 1
711 DROP TABLE t1;
712 #
713 # date and time columns
714 #
715 DROP TABLE IF EXISTS t1;
716 CREATE TABLE t1 (d DATE <CUSTOM_COL_OPTIONS> NULL,
717 dt DATETIME <CUSTOM_COL_OPTIONS> NULL,
718 ts TIMESTAMP <CUSTOM_COL_OPTIONS> NULL,
719 t TIME <CUSTOM_COL_OPTIONS> NULL,
720 y YEAR <CUSTOM_COL_OPTIONS> NULL,
721 y4 YEAR(4) <CUSTOM_COL_OPTIONS> NULL,
722 y2 YEAR(2) <CUSTOM_COL_OPTIONS> NULL
723 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
724 Warnings:
725 Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
726 SHOW COLUMNS IN t1;
727 Field Type Null Key Default Extra
728 d date # # #
729 dt datetime # # #
730 ts timestamp # # #
731 t time # # #
732 y year(4) # # #
733 y4 year(4) # # #
734 y2 year(2) # # #
735 SET @tm = '2012-04-09 05:27:00';
736 INSERT INTO t1 (d,dt,ts,t,y,y4,y2) VALUES
737 ('1000-01-01', '1000-01-01 00:00:00', FROM_UNIXTIME(1), '-838:59:59', '1901', '1901', '00'),
738 ('9999-12-31', '9999-12-31 23:59:59', FROM_UNIXTIME(2147483647), '838:59:59', '2155', '2155', '99'),
739 ('0000-00-00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '00:00:00', '0', '0', '0'),
740 (DATE(@tm),@tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),YEAR(@tm),YEAR(@tm));
741 SELECT d,dt,ts,t,y,y4,y2 FROM t1;
742 d dt ts t y y4 y2
743 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 2000 2000 00
744 1000-01-01 1000-01-01 00:00:00 1970-01-01 00:00:01 -838:59:59 1901 1901 00
745 2012-04-09 2012-04-09 05:27:00 2012-04-09 05:27:00 05:27:00 2012 2012 12
746 9999-12-31 9999-12-31 23:59:59 2038-01-19 03:14:07 838:59:59 2155 2155 99
747 INSERT INTO t1 (d,dt,ts,t,y,y4,y2) VALUES
748 ('999-13-32', '999-11-31 00:00:00', '0', '-839:00:00', '1900', '1900', '-1' );
749 Warnings:
750 Warning 1265 Data truncated for column 'd' at row 1
751 Warning 1265 Data truncated for column 'dt' at row 1
752 Warning 1265 Data truncated for column 'ts' at row 1
753 Warning 1264 Out of range value for column 't' at row 1
754 Warning 1264 Out of range value for column 'y' at row 1
755 Warning 1264 Out of range value for column 'y4' at row 1
756 Warning 1264 Out of range value for column 'y2' at row 1
757 SELECT d,dt,ts,t,y,y4,y2 FROM t1;
758 d dt ts t y y4 y2
759 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 -838:59:59 0000 0000 00
760 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 00:00:00 2000 2000 00
761 1000-01-01 1000-01-01 00:00:00 1970-01-01 00:00:01 -838:59:59 1901 1901 00
762 2012-04-09 2012-04-09 05:27:00 2012-04-09 05:27:00 05:27:00 2012 2012 12
763 9999-12-31 9999-12-31 23:59:59 2038-01-19 03:14:07 838:59:59 2155 2155 99
764 DROP TABLE t1;
765 DROP TABLE IF EXISTS t1;
766 CREATE TABLE t1 (c DATE <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
767 SHOW COLUMNS IN t1;
768 Field Type Null Key Default Extra
769 c date YES NULL
770 INSERT INTO t1 (c) VALUES (NULL);
771 SELECT COUNT(c), COUNT(*) FROM t1;
772 COUNT(c) COUNT(*)
773 0 1
774 DROP TABLE t1;
775 CREATE TABLE t1 (c DATE <CUSTOM_COL_OPTIONS> NULL,
776 c1 DATE <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
777 c2 DATE <CUSTOM_COL_OPTIONS> NULL DEFAULT '2012-12-21'
778 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
779 SHOW COLUMNS IN t1;
780 Field Type Null Key Default Extra
781 c date YES NULL
782 c1 date YES NULL
783 c2 date YES 2012-12-21
784 INSERT INTO t1 (c) VALUES (NULL);
785 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
786 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
787 1 0 0 1
788 DROP TABLE t1;
789 DROP TABLE IF EXISTS t1;
790 CREATE TABLE t1 (c DATETIME <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
791 SHOW COLUMNS IN t1;
792 Field Type Null Key Default Extra
793 c datetime YES NULL
794 INSERT INTO t1 (c) VALUES (NULL);
795 SELECT COUNT(c), COUNT(*) FROM t1;
796 COUNT(c) COUNT(*)
797 0 1
798 DROP TABLE t1;
799 CREATE TABLE t1 (c DATETIME <CUSTOM_COL_OPTIONS> NULL,
800 c1 DATETIME <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
801 c2 DATETIME <CUSTOM_COL_OPTIONS> NULL DEFAULT '2012-12-21 12:21:12'
802 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
803 SHOW COLUMNS IN t1;
804 Field Type Null Key Default Extra
805 c datetime YES NULL
806 c1 datetime YES NULL
807 c2 datetime YES 2012-12-21 12:21:12
808 INSERT INTO t1 (c) VALUES (NULL);
809 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
810 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
811 1 0 0 1
812 DROP TABLE t1;
813 DROP TABLE IF EXISTS t1;
814 CREATE TABLE t1 (c TIMESTAMP <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
815 SHOW COLUMNS IN t1;
816 Field Type Null Key Default Extra
817 c timestamp YES NULL
818 INSERT INTO t1 (c) VALUES (NULL);
819 SELECT COUNT(c), COUNT(*) FROM t1;
820 COUNT(c) COUNT(*)
821 0 1
822 DROP TABLE t1;
823 CREATE TABLE t1 (c TIMESTAMP <CUSTOM_COL_OPTIONS> NULL,
824 c1 TIMESTAMP <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
825 c2 TIMESTAMP <CUSTOM_COL_OPTIONS> NULL DEFAULT '2012-12-21 12:21:12'
826 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
827 SHOW COLUMNS IN t1;
828 Field Type Null Key Default Extra
829 c timestamp YES NULL
830 c1 timestamp YES NULL
831 c2 timestamp YES 2012-12-21 12:21:12
832 INSERT INTO t1 (c) VALUES (NULL);
833 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
834 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
835 1 0 0 1
836 DROP TABLE t1;
837 DROP TABLE IF EXISTS t1;
838 CREATE TABLE t1 (c TIME <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
839 SHOW COLUMNS IN t1;
840 Field Type Null Key Default Extra
841 c time YES NULL
842 INSERT INTO t1 (c) VALUES (NULL);
843 SELECT COUNT(c), COUNT(*) FROM t1;
844 COUNT(c) COUNT(*)
845 0 1
846 DROP TABLE t1;
847 CREATE TABLE t1 (c TIME <CUSTOM_COL_OPTIONS> NULL,
848 c1 TIME <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
849 c2 TIME <CUSTOM_COL_OPTIONS> NULL DEFAULT '12:21:12'
850 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
851 SHOW COLUMNS IN t1;
852 Field Type Null Key Default Extra
853 c time YES NULL
854 c1 time YES NULL
855 c2 time YES 12:21:12
856 INSERT INTO t1 (c) VALUES (NULL);
857 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
858 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
859 1 0 0 1
860 DROP TABLE t1;
861 DROP TABLE IF EXISTS t1;
862 CREATE TABLE t1 (c YEAR <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
863 SHOW COLUMNS IN t1;
864 Field Type Null Key Default Extra
865 c year(4) YES NULL
866 INSERT INTO t1 (c) VALUES (NULL);
867 SELECT COUNT(c), COUNT(*) FROM t1;
868 COUNT(c) COUNT(*)
869 0 1
870 DROP TABLE t1;
871 CREATE TABLE t1 (c YEAR <CUSTOM_COL_OPTIONS> NULL,
872 c1 YEAR <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
873 c2 YEAR <CUSTOM_COL_OPTIONS> NULL DEFAULT '2012'
874 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
875 SHOW COLUMNS IN t1;
876 Field Type Null Key Default Extra
877 c year(4) YES NULL
878 c1 year(4) YES NULL
879 c2 year(4) YES 2012
880 INSERT INTO t1 (c) VALUES (NULL);
881 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
882 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
883 1 0 0 1
884 DROP TABLE t1;
885 DROP TABLE IF EXISTS t1;
886 CREATE TABLE t1 (c YEAR(2) <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
887 Warnings:
888 Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
889 SHOW COLUMNS IN t1;
890 Field Type Null Key Default Extra
891 c year(2) YES NULL
892 INSERT INTO t1 (c) VALUES (NULL);
893 SELECT COUNT(c), COUNT(*) FROM t1;
894 COUNT(c) COUNT(*)
895 0 1
896 DROP TABLE t1;
897 CREATE TABLE t1 (c YEAR(2) <CUSTOM_COL_OPTIONS> NULL,
898 c1 YEAR(2) <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
899 c2 YEAR(2) <CUSTOM_COL_OPTIONS> NULL DEFAULT '12'
900 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
901 Warnings:
902 Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
903 Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
904 Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
905 SHOW COLUMNS IN t1;
906 Field Type Null Key Default Extra
907 c year(2) YES NULL
908 c1 year(2) YES NULL
909 c2 year(2) YES 12
910 INSERT INTO t1 (c) VALUES (NULL);
911 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
912 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
913 1 0 0 1
914 DROP TABLE t1;
915 #
916 # ENUM columns
917 #
918 DROP TABLE IF EXISTS t1;
919 CREATE TABLE t1 (a ENUM('') <CUSTOM_COL_OPTIONS> NULL,
920 b ENUM('test1','test2','test3','test4','test5') <CUSTOM_COL_OPTIONS> NULL,
921 c ENUM('1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',' ','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','1g','1h','1i','1j','1k','1l','1m','1n','1o','1p','1q','1r','1s','1t','1u','1v','1w','1x','1y','1z','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','2g','2h','2i','2j','2k','2l','2m','2n','2o','2p','2q','2r','2s','2t','2u','2v','2w','2x','2y','2z','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','3g','3h','3i','3j','3k','3l','3m','3n','3o','3p','3q','3r','3s','3t','3u','3v','3w','3x','3y','3z','40','41','42','43','44','45','46','47','48','49','4a','4b','4c','4d','4e','4f','4g','4h','4i','4j','4k','4l','4m','4n','4o','4p','4q','4r','4s','4t','4u','4v','4w','4x','4y','4z','50','51','52','53','54','55','56','57','58','59','5a','5b','5c','5d','5e','5f','5g','5h','5i','5j','5k','5l','5m','5n','5o','5p','5q','5r','5s','5t','5u','5v','5w','5x','5y','5z','60','61','62','63','64','65','66','67','68','69','6a','6b','6c','6d','6e','6f','6g','6h','6i','6j','6k','6l','6m','6n','6o','6p','6q','6r','6s','6t','6u','6v','6w','6x','6y','6z','70','71','72','73','74','75') <CUSTOM_COL_OPTIONS> NULL
922 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
923 SHOW COLUMNS IN t1;
924 Field Type Null Key Default Extra
925 a enum('') # # #
926 b enum('test1','test2','test3','test4','test5') # # #
927 c enum('1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','1g','1h','1i','1j','1k','1l','1m','1n','1o','1p','1q','1r','1s','1t','1u','1v','1w','1x','1y','1z','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','2g','2h','2i','2j','2k','2l','2m','2n','2o','2p','2q','2r','2s','2t','2u','2v','2w','2x','2y','2z','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','3g','3h','3i','3j','3k','3l','3m','3n','3o','3p','3q','3r','3s','3t','3u','3v','3w','3x','3y','3z','40','41','42','43','44','45','46','47','48','49','4a','4b','4c','4d','4e','4f','4g','4h','4i','4j','4k','4l','4m','4n','4o','4p','4q','4r','4s','4t','4u','4v','4w','4x','4y','4z','50','51','52','53','54','55','56','57','58','59','5a','5b','5c','5d','5e','5f','5g','5h','5i','5j','5k','5l','5m','5n','5o','5p','5q','5r','5s','5t','5u','5v','5w','5x','5y','5z','60','61','62','63','64','65','66','67','68','69','6a','6b','6c','6d','6e','6f','6g','6h','6i','6j','6k','6l','6m','6n','6o','6p','6q','6r','6s','6t','6u','6v','6w','6x','6y','6z','70','71','72','73','74','75') # # #
928 INSERT INTO t1 (a,b,c) VALUES ('','test2','4'),('',5,2);
929 SELECT a,b,c FROM t1;
930 a b c
931 test2 4
932 test5 2
933 INSERT INTO t1 (a,b,c) VALUES (0,'test6',-1);
934 Warnings:
935 Warning 1265 Data truncated for column 'a' at row 1
936 Warning 1265 Data truncated for column 'b' at row 1
937 Warning 1265 Data truncated for column 'c' at row 1
938 SELECT a,b,c FROM t1;
939 a b c
940
941 test2 4
942 test5 2
943 ALTER TABLE t1 ADD COLUMN e ENUM('a','A') <CUSTOM_COL_OPTIONS> NULL;
944 Warnings:
945 Note 1291 Column 'e' has duplicated value 'a' in ENUM
946 SHOW COLUMNS IN t1;
947 Field Type Null Key Default Extra
948 a enum('') # # #
949 b enum('test1','test2','test3','test4','test5') # # #
950 c enum('1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','1g','1h','1i','1j','1k','1l','1m','1n','1o','1p','1q','1r','1s','1t','1u','1v','1w','1x','1y','1z','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','2g','2h','2i','2j','2k','2l','2m','2n','2o','2p','2q','2r','2s','2t','2u','2v','2w','2x','2y','2z','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','3g','3h','3i','3j','3k','3l','3m','3n','3o','3p','3q','3r','3s','3t','3u','3v','3w','3x','3y','3z','40','41','42','43','44','45','46','47','48','49','4a','4b','4c','4d','4e','4f','4g','4h','4i','4j','4k','4l','4m','4n','4o','4p','4q','4r','4s','4t','4u','4v','4w','4x','4y','4z','50','51','52','53','54','55','56','57','58','59','5a','5b','5c','5d','5e','5f','5g','5h','5i','5j','5k','5l','5m','5n','5o','5p','5q','5r','5s','5t','5u','5v','5w','5x','5y','5z','60','61','62','63','64','65','66','67','68','69','6a','6b','6c','6d','6e','6f','6g','6h','6i','6j','6k','6l','6m','6n','6o','6p','6q','6r','6s','6t','6u','6v','6w','6x','6y','6z','70','71','72','73','74','75') # # #
951 e enum('a','A') # # #
952 INSERT INTO t1 (a,b,c,e) VALUES ('','test3','75','A');
953 SELECT a,b,c,e FROM t1;
954 a b c e
955 NULL
956 test2 4 NULL
957 test3 75 a
958 test5 2 NULL
959 SELECT a,b,c,e FROM t1 WHERE b='test2' OR a != '';
960 a b c e
961 test2 4 NULL
962 DROP TABLE t1;
963 DROP TABLE IF EXISTS t1;
964 CREATE TABLE t1 (c ENUM('test1','test2','test3') <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
965 SHOW COLUMNS IN t1;
966 Field Type Null Key Default Extra
967 c enum('test1','test2','test3') YES NULL
968 INSERT INTO t1 (c) VALUES (NULL);
969 SELECT COUNT(c), COUNT(*) FROM t1;
970 COUNT(c) COUNT(*)
971 0 1
972 DROP TABLE t1;
973 CREATE TABLE t1 (c ENUM('test1','test2','test3') <CUSTOM_COL_OPTIONS> NULL,
974 c1 ENUM('test1','test2','test3') <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
975 c2 ENUM('test1','test2','test3') <CUSTOM_COL_OPTIONS> NULL DEFAULT 'test2'
976 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
977 SHOW COLUMNS IN t1;
978 Field Type Null Key Default Extra
979 c enum('test1','test2','test3') YES NULL
980 c1 enum('test1','test2','test3') YES NULL
981 c2 enum('test1','test2','test3') YES test2
982 INSERT INTO t1 (c) VALUES (NULL);
983 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
984 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
985 1 0 0 1
986 DROP TABLE t1;
987 #
988 # Fixed point columns (NUMERIC, DECIMAL)
989 #
990 DROP TABLE IF EXISTS t1;
991 CREATE TABLE t1 (d DECIMAL <CUSTOM_COL_OPTIONS> NULL,
992 d0 DECIMAL(0) <CUSTOM_COL_OPTIONS> NULL,
993 d1_1 DECIMAL(1,1) <CUSTOM_COL_OPTIONS> NULL,
994 d10_2 DECIMAL(10,2) <CUSTOM_COL_OPTIONS> NULL,
995 d60_10 DECIMAL(60,10) <CUSTOM_COL_OPTIONS> NULL,
996 n NUMERIC <CUSTOM_COL_OPTIONS> NULL,
997 n0_0 NUMERIC(0,0) <CUSTOM_COL_OPTIONS> NULL,
998 n1 NUMERIC(1) <CUSTOM_COL_OPTIONS> NULL,
999 n20_4 NUMERIC(20,4) <CUSTOM_COL_OPTIONS> NULL,
1000 n65_4 NUMERIC(65,4) <CUSTOM_COL_OPTIONS> NULL
1001 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1002 SHOW COLUMNS IN t1;
1003 Field Type Null Key Default Extra
1004 d decimal(10,0) # # #
1005 d0 decimal(10,0) # # #
1006 d1_1 decimal(1,1) # # #
1007 d10_2 decimal(10,2) # # #
1008 d60_10 decimal(60,10) # # #
1009 n decimal(10,0) # # #
1010 n0_0 decimal(10,0) # # #
1011 n1 decimal(1,0) # # #
1012 n20_4 decimal(20,4) # # #
1013 n65_4 decimal(65,4) # # #
1014 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (100,123456,0.3,40000.25,123456789123456789.10001,1024,7000.0,8.0,999999.9,9223372036854775807);
1015 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (0,0,0,0,0,0,0,0,0,0);
1016 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (9999999999.0,9999999999.0,0.9,99999999.99,99999999999999999999999999999999999999999999999999.9999999999,9999999999.0,9999999999.0,9.0,9999999999999999.9999,9999999999999999999999999999999999999999999999999999999999999.9999);
1017 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
1018 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
1019 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
1020 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
1021 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1022 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (-100,-123456,-0.3,-40000.25,-123456789123456789.10001,-1024,-7000.0,-8.0,-999999.9,-9223372036854775807);
1023 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (-9999999999.0,-9999999999.0,-0.9,-99999999.99,-99999999999999999999999999999999999999999999999999.9999999999,-9999999999.0,-9999999999.0,-9.0,-9999999999999999.9999,-9999999999999999999999999999999999999999999999999999999999999.9999);
1024 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
1025 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
1026 -100 -123456 -0.3 -40000.25 -123456789123456789.1000100000 -1024 -7000 -8 -999999.9000 -9223372036854775807.0000
1027 -9999999999 -9999999999 -0.9 -99999999.99 -99999999999999999999999999999999999999999999999999.9999999999 -9999999999 -9999999999 -9 -9999999999999999.9999 -9999999999999999999999999999999999999999999999999999999999999.9999
1028 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
1029 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
1030 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1031 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1 WHERE n20_4 = 9999999999999999.9999 OR d < 100;
1032 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
1033 -100 -123456 -0.3 -40000.25 -123456789123456789.1000100000 -1024 -7000 -8 -999999.9000 -9223372036854775807.0000
1034 -9999999999 -9999999999 -0.9 -99999999.99 -99999999999999999999999999999999999999999999999999.9999999999 -9999999999 -9999999999 -9 -9999999999999999.9999 -9999999999999999999999999999999999999999999999999999999999999.9999
1035 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
1036 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1037 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) SELECT n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4 FROM t1 WHERE n65_4 = ( SELECT MAX(n65_4) FROM t1 );
1038 Warnings:
1039 Warning 1264 Out of range value for column 'd' at row 1
1040 Warning 1264 Out of range value for column 'd0' at row 1
1041 Warning 1264 Out of range value for column 'd1_1' at row 1
1042 Warning 1264 Out of range value for column 'd10_2' at row 1
1043 Warning 1264 Out of range value for column 'd60_10' at row 1
1044 Warning 1264 Out of range value for column 'n' at row 1
1045 Warning 1264 Out of range value for column 'n0_0' at row 1
1046 Warning 1264 Out of range value for column 'n1' at row 1
1047 Warning 1264 Out of range value for column 'n20_4' at row 1
1048 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
1049 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
1050 -100 -123456 -0.3 -40000.25 -123456789123456789.1000100000 -1024 -7000 -8 -999999.9000 -9223372036854775807.0000
1051 -9999999999 -9999999999 -0.9 -99999999.99 -99999999999999999999999999999999999999999999999999.9999999999 -9999999999 -9999999999 -9 -9999999999999999.9999 -9999999999999999999999999999999999999999999999999999999999999.9999
1052 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
1053 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
1054 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1055 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1056 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (10000000000.0,10000000000.0,1.1,100000000.99,100000000000000000000000000000000000000000000000000.0,10000000000.0,10000000000.0,10.0,10000000000000000.9999,10000000000000000000000000000000000000000000000000000000000000.9999);
1057 Warnings:
1058 Warning 1264 Out of range value for column 'd' at row 1
1059 Warning 1264 Out of range value for column 'd0' at row 1
1060 Warning 1264 Out of range value for column 'd1_1' at row 1
1061 Warning 1264 Out of range value for column 'd10_2' at row 1
1062 Warning 1264 Out of range value for column 'd60_10' at row 1
1063 Warning 1264 Out of range value for column 'n' at row 1
1064 Warning 1264 Out of range value for column 'n0_0' at row 1
1065 Warning 1264 Out of range value for column 'n1' at row 1
1066 Warning 1264 Out of range value for column 'n20_4' at row 1
1067 Warning 1264 Out of range value for column 'n65_4' at row 1
1068 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
1069 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
1070 -100 -123456 -0.3 -40000.25 -123456789123456789.1000100000 -1024 -7000 -8 -999999.9000 -9223372036854775807.0000
1071 -9999999999 -9999999999 -0.9 -99999999.99 -99999999999999999999999999999999999999999999999999.9999999999 -9999999999 -9999999999 -9 -9999999999999999.9999 -9999999999999999999999999999999999999999999999999999999999999.9999
1072 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
1073 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
1074 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1075 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1076 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1077 INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (9999999999.1,9999999999.1,1.9,99999999.001,99999999999999999999999999999999999999999999999999.99999999991,9999999999.1,9999999999.1,9.1,9999999999999999.00001,9999999999999999999999999999999999999999999999999999999999999.11111);
1078 Warnings:
1079 Note 1265 Data truncated for column 'd' at row 1
1080 Note 1265 Data truncated for column 'd0' at row 1
1081 Warning 1264 Out of range value for column 'd1_1' at row 1
1082 Note 1265 Data truncated for column 'd10_2' at row 1
1083 Note 1265 Data truncated for column 'd60_10' at row 1
1084 Note 1265 Data truncated for column 'n' at row 1
1085 Note 1265 Data truncated for column 'n0_0' at row 1
1086 Note 1265 Data truncated for column 'n1' at row 1
1087 Note 1265 Data truncated for column 'n20_4' at row 1
1088 Note 1265 Data truncated for column 'n65_4' at row 1
1089 SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
1090 d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
1091 -100 -123456 -0.3 -40000.25 -123456789123456789.1000100000 -1024 -7000 -8 -999999.9000 -9223372036854775807.0000
1092 -9999999999 -9999999999 -0.9 -99999999.99 -99999999999999999999999999999999999999999999999999.9999999999 -9999999999 -9999999999 -9 -9999999999999999.9999 -9999999999999999999999999999999999999999999999999999999999999.9999
1093 0 0 0.0 0.00 0.0000000000 0 0 0 0.0000 0.0000
1094 100 123456 0.3 40000.25 123456789123456789.1000100000 1024 7000 8 999999.9000 9223372036854775807.0000
1095 9999999999 9999999999 0.9 99999999.00 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.0000 9999999999999999999999999999999999999999999999999999999999999.1111
1096 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1097 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1098 9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
1099 ALTER TABLE t1 ADD COLUMN n66 NUMERIC(66);
1100 ERROR 42000: Too big precision 66 specified for 'n66'. Maximum is 65.
1101 ALTER TABLE t1 ADD COLUMN n66_6 DECIMAL(66,6);
1102 ERROR 42000: Too big precision 66 specified for 'n66_6'. Maximum is 65.
1103 ALTER TABLE t1 ADD COLUMN n66_66 DECIMAL(66,66);
1104 ERROR 42000: Too big scale 66 specified for 'n66_66'. Maximum is 30.
1105 DROP TABLE t1;
1106 DROP TABLE IF EXISTS t1;
1107 CREATE TABLE t1 (c DECIMAL <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1108 SHOW COLUMNS IN t1;
1109 Field Type Null Key Default Extra
1110 c decimal(10,0) YES NULL
1111 INSERT INTO t1 (c) VALUES (NULL);
1112 SELECT COUNT(c), COUNT(*) FROM t1;
1113 COUNT(c) COUNT(*)
1114 0 1
1115 DROP TABLE t1;
1116 CREATE TABLE t1 (c DECIMAL <CUSTOM_COL_OPTIONS> NULL,
1117 c1 DECIMAL <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
1118 c2 DECIMAL <CUSTOM_COL_OPTIONS> NULL DEFAULT 1.1
1119 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1120 Warnings:
1121 Note 1265 Data truncated for column 'c2' at row 1
1122 SHOW COLUMNS IN t1;
1123 Field Type Null Key Default Extra
1124 c decimal(10,0) YES NULL
1125 c1 decimal(10,0) YES NULL
1126 c2 decimal(10,0) YES 1
1127 INSERT INTO t1 (c) VALUES (NULL);
1128 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
1129 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
1130 1 0 0 1
1131 DROP TABLE t1;
1132 DROP TABLE IF EXISTS t1;
1133 CREATE TABLE t1 (c NUMERIC <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1134 SHOW COLUMNS IN t1;
1135 Field Type Null Key Default Extra
1136 c decimal(10,0) YES NULL
1137 INSERT INTO t1 (c) VALUES (NULL);
1138 SELECT COUNT(c), COUNT(*) FROM t1;
1139 COUNT(c) COUNT(*)
1140 0 1
1141 DROP TABLE t1;
1142 CREATE TABLE t1 (c NUMERIC <CUSTOM_COL_OPTIONS> NULL,
1143 c1 NUMERIC <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
1144 c2 NUMERIC <CUSTOM_COL_OPTIONS> NULL DEFAULT 0
1145 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1146 SHOW COLUMNS IN t1;
1147 Field Type Null Key Default Extra
1148 c decimal(10,0) YES NULL
1149 c1 decimal(10,0) YES NULL
1150 c2 decimal(10,0) YES 0
1151 INSERT INTO t1 (c) VALUES (NULL);
1152 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
1153 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
1154 1 0 0 1
1155 DROP TABLE t1;
1156 #
1157 # Floating point columns (FLOAT, DOUBLE)
1158 #
1159 DROP TABLE IF EXISTS t1;
1160 CREATE TABLE t1 (f FLOAT <CUSTOM_COL_OPTIONS> NULL,
1161 f0 FLOAT(0) <CUSTOM_COL_OPTIONS> NULL,
1162 r1_1 REAL(1,1) <CUSTOM_COL_OPTIONS> NULL,
1163 f23_0 FLOAT(23) <CUSTOM_COL_OPTIONS> NULL,
1164 f20_3 FLOAT(20,3) <CUSTOM_COL_OPTIONS> NULL,
1165 d DOUBLE <CUSTOM_COL_OPTIONS> NULL,
1166 d1_0 DOUBLE(1,0) <CUSTOM_COL_OPTIONS> NULL,
1167 d10_10 DOUBLE PRECISION (10,10) <CUSTOM_COL_OPTIONS> NULL,
1168 d53 DOUBLE(53,0) <CUSTOM_COL_OPTIONS> NULL,
1169 d53_10 DOUBLE(53,10) <CUSTOM_COL_OPTIONS> NULL
1170 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1171 SHOW COLUMNS IN t1;
1172 Field Type Null Key Default Extra
1173 f float # # #
1174 f0 float # # #
1175 r1_1 double(1,1) # # #
1176 f23_0 float # # #
1177 f20_3 float(20,3) # # #
1178 d double # # #
1179 d1_0 double(1,0) # # #
1180 d10_10 double(10,10) # # #
1181 d53 double(53,0) # # #
1182 d53_10 double(53,10) # # #
1183 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (12345.12345,12345.12345,0.9,123456789.123,56789.987,11111111.111,8.0,0.0123456789,1234566789123456789,99999999999999999.99999999);
1184 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
1185 f 12345.1
1186 d 11111111.111
1187 d10_10 0.0123456789
1188 d1_0 8
1189 d53 1234566789123456800
1190 d53_10 100000000000000000.0000000000
1191 f0 12345.1
1192 f20_3 56789.988
1193 f23_0 123457000
1194 r1_1 0.9
1195 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (0,0,0,0,0,0,0,0,0,0);
1196 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (
1197 99999999999999999999999999999999999999,
1198 99999999999999999999999999999999999999.9999999999999999,
1199 0.9,
1200 99999999999999999999999999999999999999.9,
1201 99999999999999999.999,
1202 999999999999999999999999999999999999999999999999999999999999999999999999999999999,
1203 9,
1204 0.9999999999,
1205 1999999999999999999999999999999999999999999999999999999,
1206 19999999999999999999999999999999999999999999.9999999999
1207 );
1208 Warnings:
1209 Warning 1264 Out of range value for column 'd53' at row 1
1210 Warning 1264 Out of range value for column 'd53_10' at row 1
1211 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
1212 f 12345.1
1213 d 0
1214 d 11111111.111
1215 d 1e81
1216 d10_10 0.0000000000
1217 d10_10 0.0123456789
1218 d10_10 0.9999999999
1219 d1_0 0
1220 d1_0 8
1221 d1_0 9
1222 d53 0
1223 d53 100000000000000000000000000000000000000000000000000000
1224 d53 1234566789123456800
1225 d53_10 0.0000000000
1226 d53_10 100000000000000000.0000000000
1227 d53_10 10000000000000000000000000000000000000000000.0000000000
1228 f 0
1229 f 1e38
1230 f0 0
1231 f0 12345.1
1232 f0 1e38
1233 f20_3 0.000
1234 f20_3 56789.988
1235 f20_3 99999998430674940.000
1236 f23_0 0
1237 f23_0 123457000
1238 f23_0 1e38
1239 r1_1 0.0
1240 r1_1 0.9
1241 r1_1 0.9
1242 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (-999999999999999999999999,-99999999999.999999999999,-0.9,-999.99999999999999999999,-99999999999999999.999,-999999999999999999999999999999999999999999999999999999999999-0.999,-9,-.9999999999,-999999999999999999999999999999.99999999999999999999999,-9999999999999999999999999999999999999999999.9999999999);
1243 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
1244 f 12345.1
1245 d -1e60
1246 d 0
1247 d 11111111.111
1248 d 1e81
1249 d10_10 -0.9999999999
1250 d10_10 0.0000000000
1251 d10_10 0.0123456789
1252 d10_10 0.9999999999
1253 d1_0 -9
1254 d1_0 0
1255 d1_0 8
1256 d1_0 9
1257 d53 -1000000000000000000000000000000
1258 d53 0
1259 d53 100000000000000000000000000000000000000000000000000000
1260 d53 1234566789123456800
1261 d53_10 -10000000000000000000000000000000000000000000.0000000000
1262 d53_10 0.0000000000
1263 d53_10 100000000000000000.0000000000
1264 d53_10 10000000000000000000000000000000000000000000.0000000000
1265 f -1e24
1266 f 0
1267 f 1e38
1268 f0 -100000000000
1269 f0 0
1270 f0 12345.1
1271 f0 1e38
1272 f20_3 -99999998430674940.000
1273 f20_3 0.000
1274 f20_3 56789.988
1275 f20_3 99999998430674940.000
1276 f23_0 -1000
1277 f23_0 0
1278 f23_0 123457000
1279 f23_0 1e38
1280 r1_1 -0.9
1281 r1_1 0.0
1282 r1_1 0.9
1283 r1_1 0.9
1284 SELECT MAX(f), MAX(f0), MAX(r1_1), MAX(f23_0), MAX(f20_3), MAX(d), MAX(d1_0), MAX(d10_10), MAX(d53), MAX(d53_10) FROM t1;
1285 MAX(f) 9.999999680285692e37
1286 MAX(d) 1e81
1287 MAX(d10_10) 0.9999999999
1288 MAX(d1_0) 9
1289 MAX(d53) 100000000000000000000000000000000000000000000000000000
1290 MAX(d53_10) 10000000000000000000000000000000000000000000.0000000000
1291 MAX(f0) 9.999999680285692e37
1292 MAX(f20_3) 99999998430674940.000
1293 MAX(f23_0) 9.999999680285692e37
1294 MAX(r1_1) 0.9
1295 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) SELECT d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10 FROM t1 ORDER BY d53_10 DESC LIMIT 1;
1296 Warnings:
1297 Warning 1264 Out of range value for column 'f' at row 1
1298 Warning 1264 Out of range value for column 'f0' at row 1
1299 Warning 1264 Out of range value for column 'r1_1' at row 1
1300 Warning 1264 Out of range value for column 'f23_0' at row 1
1301 Warning 1264 Out of range value for column 'f20_3' at row 1
1302 Warning 1264 Out of range value for column 'd1_0' at row 1
1303 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
1304 f 12345.1
1305 d -1e60
1306 d 0
1307 d 11111111.111
1308 d 1e43
1309 d 1e81
1310 d10_10 -0.9999999999
1311 d10_10 0.0000000000
1312 d10_10 0.0123456789
1313 d10_10 0.9999999999
1314 d10_10 10000000000000000000000000000000000000000000.0000000000
1315 d1_0 -9
1316 d1_0 0
1317 d1_0 8
1318 d1_0 9
1319 d1_0 9
1320 d53 -1000000000000000000000000000000
1321 d53 0
1322 d53 10000000000000000000000000000000000000000000
1323 d53 100000000000000000000000000000000000000000000000000000
1324 d53 1234566789123456800
1325 d53_10 -10000000000000000000000000000000000000000000.0000000000
1326 d53_10 0.0000000000
1327 d53_10 100000000000000000.0000000000
1328 d53_10 10000000000000000000000000000000000000000000.0000000000
1329 d53_10 10000000000000000000000000000000000000000000.0000000000
1330 f -1e24
1331 f 0
1332 f 1e38
1333 f 3.40282e38
1334 f0 -100000000000
1335 f0 0
1336 f0 12345.1
1337 f0 1e38
1338 f0 3.40282e38
1339 f20_3 -99999998430674940.000
1340 f20_3 0.000
1341 f20_3 56789.988
1342 f20_3 99999998430674940.000
1343 f20_3 99999998430674940.000
1344 f23_0 -1000
1345 f23_0 0
1346 f23_0 123457000
1347 f23_0 1e38
1348 f23_0 3.40282e38
1349 r1_1 -0.9
1350 r1_1 0.0
1351 r1_1 0.9
1352 r1_1 0.9
1353 r1_1 0.9
1354 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (
1355 999999999999999999999999999999999999999,
1356 999999999999999999999999999999999999999.9999999999999999,
1357 1.9,
1358 999999999999999999999999999999999999999.9,
1359 999999999999999999.999,
1360 9999999999999999999999999999999999999999999999999999999999999999999999999999999999,
1361 99,
1362 1.9999999999,
1363 1999999999999999999999999999999999999999999999999999999,
1364 19999999999999999999999999999999999999999999.9999999999
1365 );
1366 Warnings:
1367 Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated
1368 Warning 1264 Out of range value for column 'f' at row 1
1369 Warning 1264 Out of range value for column 'f0' at row 1
1370 Warning 1264 Out of range value for column 'r1_1' at row 1
1371 Warning 1264 Out of range value for column 'f23_0' at row 1
1372 Warning 1264 Out of range value for column 'f20_3' at row 1
1373 Warning 1264 Out of range value for column 'd1_0' at row 1
1374 Warning 1264 Out of range value for column 'd10_10' at row 1
1375 Warning 1264 Out of range value for column 'd53' at row 1
1376 Warning 1264 Out of range value for column 'd53_10' at row 1
1377 SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
1378 f 12345.1
1379 d -1e60
1380 d 0
1381 d 11111111.111
1382 d 1e43
1383 d 1e65
1384 d 1e81
1385 d10_10 -0.9999999999
1386 d10_10 0.0000000000
1387 d10_10 0.0123456789
1388 d10_10 0.9999999999
1389 d10_10 0.9999999999
1390 d10_10 10000000000000000000000000000000000000000000.0000000000
1391 d1_0 -9
1392 d1_0 0
1393 d1_0 8
1394 d1_0 9
1395 d1_0 9
1396 d1_0 9
1397 d53 -1000000000000000000000000000000
1398 d53 0
1399 d53 10000000000000000000000000000000000000000000
1400 d53 100000000000000000000000000000000000000000000000000000
1401 d53 100000000000000000000000000000000000000000000000000000
1402 d53 1234566789123456800
1403 d53_10 -10000000000000000000000000000000000000000000.0000000000
1404 d53_10 0.0000000000
1405 d53_10 100000000000000000.0000000000
1406 d53_10 10000000000000000000000000000000000000000000.0000000000
1407 d53_10 10000000000000000000000000000000000000000000.0000000000
1408 d53_10 10000000000000000000000000000000000000000000.0000000000
1409 f -1e24
1410 f 0
1411 f 1e38
1412 f 3.40282e38
1413 f 3.40282e38
1414 f0 -100000000000
1415 f0 0
1416 f0 12345.1
1417 f0 1e38
1418 f0 3.40282e38
1419 f0 3.40282e38
1420 f20_3 -99999998430674940.000
1421 f20_3 0.000
1422 f20_3 56789.988
1423 f20_3 99999998430674940.000
1424 f20_3 99999998430674940.000
1425 f20_3 99999998430674940.000
1426 f23_0 -1000
1427 f23_0 0
1428 f23_0 123457000
1429 f23_0 1e38
1430 f23_0 3.40282e38
1431 f23_0 3.40282e38
1432 r1_1 -0.9
1433 r1_1 0.0
1434 r1_1 0.9
1435 r1_1 0.9
1436 r1_1 0.9
1437 r1_1 0.9
1438 ALTER TABLE t1 ADD COLUMN d0_0 DOUBLE(0,0);
1439 # ERROR: Statement succeeded (expected results: ER_TOO_BIG_DISPLAYWIDTH)
1440 # ------------ UNEXPECTED RESULT ------------
1441 # [ ALTER TABLE t1 ADD COLUMN d0_0 DOUBLE(0,0) ]
1442 # The statement|command succeeded unexpectedly.
1443 # ALTER TABLE or the mix could be unsupported|malfunctioning, or the problem was caused by previous errors.
1444 # You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def.
1445 # Further in this test, the message might sometimes be suppressed; a part of the test might be skipped.
1446 # Also, this problem may cause a chain effect (more errors of different kinds in the test).
1447 # -------------------------------------------
1448 ALTER TABLE t1 ADD COLUMN n66_6 DECIMAL(256,1);
1449 ERROR 42000: Too big precision 256 specified for 'n66_6'. Maximum is 65.
1450 ALTER TABLE t1 ADD COLUMN n66_66 DECIMAL(40,35);
1451 ERROR 42000: Too big scale 35 specified for 'n66_66'. Maximum is 30.
1452 DROP TABLE t1;
1453 DROP TABLE IF EXISTS t1;
1454 CREATE TABLE t1 (c FLOAT <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1455 SHOW COLUMNS IN t1;
1456 Field Type Null Key Default Extra
1457 c float YES NULL
1458 INSERT INTO t1 (c) VALUES (NULL);
1459 SELECT COUNT(c), COUNT(*) FROM t1;
1460 COUNT(c) COUNT(*)
1461 0 1
1462 DROP TABLE t1;
1463 CREATE TABLE t1 (c FLOAT <CUSTOM_COL_OPTIONS> NULL,
1464 c1 FLOAT <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
1465 c2 FLOAT <CUSTOM_COL_OPTIONS> NULL DEFAULT 1.1
1466 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1467 SHOW COLUMNS IN t1;
1468 Field Type Null Key Default Extra
1469 c float YES NULL
1470 c1 float YES NULL
1471 c2 float YES 1.1
1472 INSERT INTO t1 (c) VALUES (NULL);
1473 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
1474 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
1475 1 0 0 1
1476 DROP TABLE t1;
1477 DROP TABLE IF EXISTS t1;
1478 CREATE TABLE t1 (c DOUBLE <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1479 SHOW COLUMNS IN t1;
1480 Field Type Null Key Default Extra
1481 c double YES NULL
1482 INSERT INTO t1 (c) VALUES (NULL);
1483 SELECT COUNT(c), COUNT(*) FROM t1;
1484 COUNT(c) COUNT(*)
1485 0 1
1486 DROP TABLE t1;
1487 CREATE TABLE t1 (c DOUBLE <CUSTOM_COL_OPTIONS> NULL,
1488 c1 DOUBLE <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
1489 c2 DOUBLE <CUSTOM_COL_OPTIONS> NULL DEFAULT 0
1490 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1491 SHOW COLUMNS IN t1;
1492 Field Type Null Key Default Extra
1493 c double YES NULL
1494 c1 double YES NULL
1495 c2 double YES 0
1496 INSERT INTO t1 (c) VALUES (NULL);
1497 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
1498 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
1499 1 0 0 1
1500 DROP TABLE t1;
1501 #
1502 # INT columns
1503 #
1504 DROP TABLE IF EXISTS t1;
1505 CREATE TABLE t1 (i INT <CUSTOM_COL_OPTIONS> NULL,
1506 i0 INT(0) <CUSTOM_COL_OPTIONS> NULL,
1507 i1 INT(1) <CUSTOM_COL_OPTIONS> NULL,
1508 i20 INT(20) <CUSTOM_COL_OPTIONS> NULL,
1509 t TINYINT <CUSTOM_COL_OPTIONS> NULL,
1510 t0 TINYINT(0) <CUSTOM_COL_OPTIONS> NULL,
1511 t1 TINYINT(1) <CUSTOM_COL_OPTIONS> NULL,
1512 t20 TINYINT(20) <CUSTOM_COL_OPTIONS> NULL,
1513 s SMALLINT <CUSTOM_COL_OPTIONS> NULL,
1514 s0 SMALLINT(0) <CUSTOM_COL_OPTIONS> NULL,
1515 s1 SMALLINT(1) <CUSTOM_COL_OPTIONS> NULL,
1516 s20 SMALLINT(20) <CUSTOM_COL_OPTIONS> NULL,
1517 m MEDIUMINT <CUSTOM_COL_OPTIONS> NULL,
1518 m0 MEDIUMINT(0) <CUSTOM_COL_OPTIONS> NULL,
1519 m1 MEDIUMINT(1) <CUSTOM_COL_OPTIONS> NULL,
1520 m20 MEDIUMINT(20) <CUSTOM_COL_OPTIONS> NULL,
1521 b BIGINT <CUSTOM_COL_OPTIONS> NULL,
1522 b0 BIGINT(0) <CUSTOM_COL_OPTIONS> NULL,
1523 b1 BIGINT(1) <CUSTOM_COL_OPTIONS> NULL,
1524 b20 BIGINT(20) <CUSTOM_COL_OPTIONS> NULL
1525 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1526 SHOW COLUMNS IN t1;
1527 Field Type Null Key Default Extra
1528 i int(11) # # #
1529 i0 int(11) # # #
1530 i1 int(1) # # #
1531 i20 int(20) # # #
1532 t tinyint(4) # # #
1533 t0 tinyint(4) # # #
1534 t1 tinyint(1) # # #
1535 t20 tinyint(20) # # #
1536 s smallint(6) # # #
1537 s0 smallint(6) # # #
1538 s1 smallint(1) # # #
1539 s20 smallint(20) # # #
1540 m mediumint(9) # # #
1541 m0 mediumint(9) # # #
1542 m1 mediumint(1) # # #
1543 m20 mediumint(20) # # #
1544 b bigint(20) # # #
1545 b0 bigint(20) # # #
1546 b1 bigint(1) # # #
1547 b20 bigint(20) # # #
1548 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);
1549 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
1550 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (2147483647,2147483647,2147483647,2147483647,127,127,127,127,32767,32767,32767,32767,8388607,8388607,8388607,8388607,9223372036854775807,9223372036854775807,9223372036854775807,9223372036854775807);
1551 SELECT i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20 FROM t1;
1552 i i0 i1 i20 t t0 t1 t20 s s0 s1 s20 m m0 m1 m20 b b0 b1 b20
1553 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1554 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
1555 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1556 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (-2147483648,-2147483648,-2147483648,-2147483648,-128,-128,-128,-128,-32768,-32768,-32768,-32768,-8388608,-8388608,-8388608,-8388608,-9223372036854775808,-9223372036854775808,-9223372036854775808,-9223372036854775808);
1557 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (4294967295,4294967295,4294967295,4294967295,255,255,255,255,65535,65535,65535,65535,16777215,16777215,16777215,16777215,18446744073709551615,18446744073709551615,18446744073709551615,18446744073709551615);
1558 Warnings:
1559 Warning 1264 Out of range value for column 'i' at row 1
1560 Warning 1264 Out of range value for column 'i0' at row 1
1561 Warning 1264 Out of range value for column 'i1' at row 1
1562 Warning 1264 Out of range value for column 'i20' at row 1
1563 Warning 1264 Out of range value for column 't' at row 1
1564 Warning 1264 Out of range value for column 't0' at row 1
1565 Warning 1264 Out of range value for column 't1' at row 1
1566 Warning 1264 Out of range value for column 't20' at row 1
1567 Warning 1264 Out of range value for column 's' at row 1
1568 Warning 1264 Out of range value for column 's0' at row 1
1569 Warning 1264 Out of range value for column 's1' at row 1
1570 Warning 1264 Out of range value for column 's20' at row 1
1571 Warning 1264 Out of range value for column 'm' at row 1
1572 Warning 1264 Out of range value for column 'm0' at row 1
1573 Warning 1264 Out of range value for column 'm1' at row 1
1574 Warning 1264 Out of range value for column 'm20' at row 1
1575 Warning 1264 Out of range value for column 'b' at row 1
1576 Warning 1264 Out of range value for column 'b0' at row 1
1577 Warning 1264 Out of range value for column 'b1' at row 1
1578 Warning 1264 Out of range value for column 'b20' at row 1
1579 SELECT i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20 FROM t1;
1580 i i0 i1 i20 t t0 t1 t20 s s0 s1 s20 m m0 m1 m20 b b0 b1 b20
1581 -2147483648 -2147483648 -2147483648 -2147483648 -128 -128 -128 -128 -32768 -32768 -32768 -32768 -8388608 -8388608 -8388608 -8388608 -9223372036854775808 -9223372036854775808 -9223372036854775808 -9223372036854775808
1582 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1583 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
1584 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1585 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1586 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (-2147483649,-2147483649,-2147483649,-2147483649,-129,-129,-129,-129,-32769,-32769,-32769,-32769,-8388609,-8388609,-8388609,-8388609,-9223372036854775809,-9223372036854775809,-9223372036854775809,-9223372036854775809);
1587 Warnings:
1588 Warning 1264 Out of range value for column 'i' at row 1
1589 Warning 1264 Out of range value for column 'i0' at row 1
1590 Warning 1264 Out of range value for column 'i1' at row 1
1591 Warning 1264 Out of range value for column 'i20' at row 1
1592 Warning 1264 Out of range value for column 't' at row 1
1593 Warning 1264 Out of range value for column 't0' at row 1
1594 Warning 1264 Out of range value for column 't1' at row 1
1595 Warning 1264 Out of range value for column 't20' at row 1
1596 Warning 1264 Out of range value for column 's' at row 1
1597 Warning 1264 Out of range value for column 's0' at row 1
1598 Warning 1264 Out of range value for column 's1' at row 1
1599 Warning 1264 Out of range value for column 's20' at row 1
1600 Warning 1264 Out of range value for column 'm' at row 1
1601 Warning 1264 Out of range value for column 'm0' at row 1
1602 Warning 1264 Out of range value for column 'm1' at row 1
1603 Warning 1264 Out of range value for column 'm20' at row 1
1604 Warning 1264 Out of range value for column 'b' at row 1
1605 Warning 1264 Out of range value for column 'b0' at row 1
1606 Warning 1264 Out of range value for column 'b1' at row 1
1607 Warning 1264 Out of range value for column 'b20' at row 1
1608 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (4294967296,4294967296,4294967296,4294967296,256,256,256,256,65536,65536,65536,65536,16777216,16777216,16777216,16777216,18446744073709551616,18446744073709551616,18446744073709551616,18446744073709551616);
1609 Warnings:
1610 Warning 1264 Out of range value for column 'i' at row 1
1611 Warning 1264 Out of range value for column 'i0' at row 1
1612 Warning 1264 Out of range value for column 'i1' at row 1
1613 Warning 1264 Out of range value for column 'i20' at row 1
1614 Warning 1264 Out of range value for column 't' at row 1
1615 Warning 1264 Out of range value for column 't0' at row 1
1616 Warning 1264 Out of range value for column 't1' at row 1
1617 Warning 1264 Out of range value for column 't20' at row 1
1618 Warning 1264 Out of range value for column 's' at row 1
1619 Warning 1264 Out of range value for column 's0' at row 1
1620 Warning 1264 Out of range value for column 's1' at row 1
1621 Warning 1264 Out of range value for column 's20' at row 1
1622 Warning 1264 Out of range value for column 'm' at row 1
1623 Warning 1264 Out of range value for column 'm0' at row 1
1624 Warning 1264 Out of range value for column 'm1' at row 1
1625 Warning 1264 Out of range value for column 'm20' at row 1
1626 Warning 1264 Out of range value for column 'b' at row 1
1627 Warning 1264 Out of range value for column 'b0' at row 1
1628 Warning 1264 Out of range value for column 'b1' at row 1
1629 Warning 1264 Out of range value for column 'b20' at row 1
1630 INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) SELECT b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b FROM t1 WHERE b IN (-9223372036854775808,9223372036854775807,18446744073709551615);
1631 Warnings:
1632 Warning 1264 Out of range value for column 'i' at row 1
1633 Warning 1264 Out of range value for column 'i0' at row 1
1634 Warning 1264 Out of range value for column 'i1' at row 1
1635 Warning 1264 Out of range value for column 'i20' at row 1
1636 Warning 1264 Out of range value for column 't' at row 1
1637 Warning 1264 Out of range value for column 't0' at row 1
1638 Warning 1264 Out of range value for column 't1' at row 1
1639 Warning 1264 Out of range value for column 't20' at row 1
1640 Warning 1264 Out of range value for column 's' at row 1
1641 Warning 1264 Out of range value for column 's0' at row 1
1642 Warning 1264 Out of range value for column 's1' at row 1
1643 Warning 1264 Out of range value for column 's20' at row 1
1644 Warning 1264 Out of range value for column 'm' at row 1
1645 Warning 1264 Out of range value for column 'm0' at row 1
1646 Warning 1264 Out of range value for column 'm1' at row 1
1647 Warning 1264 Out of range value for column 'm20' at row 1
1648 Warning 1264 Out of range value for column 'i' at row 2
1649 Warning 1264 Out of range value for column 'i0' at row 2
1650 Warning 1264 Out of range value for column 'i1' at row 2
1651 Warning 1264 Out of range value for column 'i20' at row 2
1652 Warning 1264 Out of range value for column 't' at row 2
1653 Warning 1264 Out of range value for column 't0' at row 2
1654 Warning 1264 Out of range value for column 't1' at row 2
1655 Warning 1264 Out of range value for column 't20' at row 2
1656 Warning 1264 Out of range value for column 's' at row 2
1657 Warning 1264 Out of range value for column 's0' at row 2
1658 Warning 1264 Out of range value for column 's1' at row 2
1659 Warning 1264 Out of range value for column 's20' at row 2
1660 Warning 1264 Out of range value for column 'm' at row 2
1661 Warning 1264 Out of range value for column 'm0' at row 2
1662 Warning 1264 Out of range value for column 'm1' at row 2
1663 Warning 1264 Out of range value for column 'm20' at row 2
1664 Warning 1264 Out of range value for column 'i' at row 3
1665 Warning 1264 Out of range value for column 'i0' at row 3
1666 Warning 1264 Out of range value for column 'i1' at row 3
1667 Warning 1264 Out of range value for column 'i20' at row 3
1668 Warning 1264 Out of range value for column 't' at row 3
1669 Warning 1264 Out of range value for column 't0' at row 3
1670 Warning 1264 Out of range value for column 't1' at row 3
1671 Warning 1264 Out of range value for column 't20' at row 3
1672 Warning 1264 Out of range value for column 's' at row 3
1673 Warning 1264 Out of range value for column 's0' at row 3
1674 Warning 1264 Out of range value for column 's1' at row 3
1675 Warning 1264 Out of range value for column 's20' at row 3
1676 Warning 1264 Out of range value for column 'm' at row 3
1677 Warning 1264 Out of range value for column 'm0' at row 3
1678 Warning 1264 Out of range value for column 'm1' at row 3
1679 Warning 1264 Out of range value for column 'm20' at row 3
1680 Warning 1264 Out of range value for column 'i' at row 4
1681 Warning 1264 Out of range value for column 'i0' at row 4
1682 Warning 1264 Out of range value for column 'i1' at row 4
1683 Warning 1264 Out of range value for column 'i20' at row 4
1684 Warning 1264 Out of range value for column 't' at row 4
1685 Warning 1264 Out of range value for column 't0' at row 4
1686 Warning 1264 Out of range value for column 't1' at row 4
1687 Warning 1264 Out of range value for column 't20' at row 4
1688 Warning 1264 Out of range value for column 's' at row 4
1689 Warning 1264 Out of range value for column 's0' at row 4
1690 Warning 1264 Out of range value for column 's1' at row 4
1691 Warning 1264 Out of range value for column 's20' at row 4
1692 Warning 1264 Out of range value for column 'm' at row 4
1693 Warning 1264 Out of range value for column 'm0' at row 4
1694 Warning 1264 Out of range value for column 'm1' at row 4
1695 Warning 1264 Out of range value for column 'm20' at row 4
1696 SELECT i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20 FROM t1;
1697 i i0 i1 i20 t t0 t1 t20 s s0 s1 s20 m m0 m1 m20 b b0 b1 b20
1698 -2147483648 -2147483648 -2147483648 -2147483648 -128 -128 -128 -128 -32768 -32768 -32768 -32768 -8388608 -8388608 -8388608 -8388608 -9223372036854775808 -9223372036854775808 -9223372036854775808 -9223372036854775808
1699 -2147483648 -2147483648 -2147483648 -2147483648 -128 -128 -128 -128 -32768 -32768 -32768 -32768 -8388608 -8388608 -8388608 -8388608 -9223372036854775808 -9223372036854775808 -9223372036854775808 -9223372036854775808
1700 -2147483648 -2147483648 -2147483648 -2147483648 -128 -128 -128 -128 -32768 -32768 -32768 -32768 -8388608 -8388608 -8388608 -8388608 -9223372036854775808 -9223372036854775808 -9223372036854775808 -9223372036854775808
1701 -2147483648 -2147483648 -2147483648 -2147483648 -128 -128 -128 -128 -32768 -32768 -32768 -32768 -8388608 -8388608 -8388608 -8388608 -9223372036854775808 -9223372036854775808 -9223372036854775808 -9223372036854775808
1702 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1703 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
1704 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1705 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1706 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1707 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1708 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1709 2147483647 2147483647 2147483647 2147483647 127 127 127 127 32767 32767 32767 32767 8388607 8388607 8388607 8388607 9223372036854775807 9223372036854775807 9223372036854775807 9223372036854775807
1710 ALTER TABLE t1 ADD COLUMN i257 INT(257);
1711 ERROR 42000: Display width out of range for 'i257' (max = 255)
1712 DROP TABLE t1;
1713 DROP TABLE IF EXISTS t1;
1714 CREATE TABLE t1 (c INT <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1715 SHOW COLUMNS IN t1;
1716 Field Type Null Key Default Extra
1717 c int(11) YES NULL
1718 INSERT INTO t1 (c) VALUES (NULL);
1719 SELECT COUNT(c), COUNT(*) FROM t1;
1720 COUNT(c) COUNT(*)
1721 0 1
1722 DROP TABLE t1;
1723 CREATE TABLE t1 (c INT <CUSTOM_COL_OPTIONS> NULL,
1724 c1 INT <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
1725 c2 INT <CUSTOM_COL_OPTIONS> NULL DEFAULT 2147483647
1726 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1727 SHOW COLUMNS IN t1;
1728 Field Type Null Key Default Extra
1729 c int(11) YES NULL
1730 c1 int(11) YES NULL
1731 c2 int(11) YES 2147483647
1732 INSERT INTO t1 (c) VALUES (NULL);
1733 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
1734 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
1735 1 0 0 1
1736 DROP TABLE t1;
1737 DROP TABLE IF EXISTS t1;
1738 CREATE TABLE t1 (c TINYINT <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1739 SHOW COLUMNS IN t1;
1740 Field Type Null Key Default Extra
1741 c tinyint(4) YES NULL
1742 INSERT INTO t1 (c) VALUES (NULL);
1743 SELECT COUNT(c), COUNT(*) FROM t1;
1744 COUNT(c) COUNT(*)
1745 0 1
1746 DROP TABLE t1;
1747 CREATE TABLE t1 (c TINYINT <CUSTOM_COL_OPTIONS> NULL,
1748 c1 TINYINT <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
1749 c2 TINYINT <CUSTOM_COL_OPTIONS> NULL DEFAULT 127
1750 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1751 SHOW COLUMNS IN t1;
1752 Field Type Null Key Default Extra
1753 c tinyint(4) YES NULL
1754 c1 tinyint(4) YES NULL
1755 c2 tinyint(4) YES 127
1756 INSERT INTO t1 (c) VALUES (NULL);
1757 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
1758 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
1759 1 0 0 1
1760 DROP TABLE t1;
1761 DROP TABLE IF EXISTS t1;
1762 CREATE TABLE t1 (c SMALLINT <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1763 SHOW COLUMNS IN t1;
1764 Field Type Null Key Default Extra
1765 c smallint(6) YES NULL
1766 INSERT INTO t1 (c) VALUES (NULL);
1767 SELECT COUNT(c), COUNT(*) FROM t1;
1768 COUNT(c) COUNT(*)
1769 0 1
1770 DROP TABLE t1;
1771 CREATE TABLE t1 (c SMALLINT <CUSTOM_COL_OPTIONS> NULL,
1772 c1 SMALLINT <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
1773 c2 SMALLINT <CUSTOM_COL_OPTIONS> NULL DEFAULT 0
1774 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1775 SHOW COLUMNS IN t1;
1776 Field Type Null Key Default Extra
1777 c smallint(6) YES NULL
1778 c1 smallint(6) YES NULL
1779 c2 smallint(6) YES 0
1780 INSERT INTO t1 (c) VALUES (NULL);
1781 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
1782 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
1783 1 0 0 1
1784 DROP TABLE t1;
1785 DROP TABLE IF EXISTS t1;
1786 CREATE TABLE t1 (c MEDIUMINT <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1787 SHOW COLUMNS IN t1;
1788 Field Type Null Key Default Extra
1789 c mediumint(9) YES NULL
1790 INSERT INTO t1 (c) VALUES (NULL);
1791 SELECT COUNT(c), COUNT(*) FROM t1;
1792 COUNT(c) COUNT(*)
1793 0 1
1794 DROP TABLE t1;
1795 CREATE TABLE t1 (c MEDIUMINT <CUSTOM_COL_OPTIONS> NULL,
1796 c1 MEDIUMINT <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
1797 c2 MEDIUMINT <CUSTOM_COL_OPTIONS> NULL DEFAULT 1
1798 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1799 SHOW COLUMNS IN t1;
1800 Field Type Null Key Default Extra
1801 c mediumint(9) YES NULL
1802 c1 mediumint(9) YES NULL
1803 c2 mediumint(9) YES 1
1804 INSERT INTO t1 (c) VALUES (NULL);
1805 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
1806 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
1807 1 0 0 1
1808 DROP TABLE t1;
1809 DROP TABLE IF EXISTS t1;
1810 CREATE TABLE t1 (c BIGINT <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1811 SHOW COLUMNS IN t1;
1812 Field Type Null Key Default Extra
1813 c bigint(20) YES NULL
1814 INSERT INTO t1 (c) VALUES (NULL);
1815 SELECT COUNT(c), COUNT(*) FROM t1;
1816 COUNT(c) COUNT(*)
1817 0 1
1818 DROP TABLE t1;
1819 CREATE TABLE t1 (c BIGINT <CUSTOM_COL_OPTIONS> NULL,
1820 c1 BIGINT <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
1821 c2 BIGINT <CUSTOM_COL_OPTIONS> NULL DEFAULT 9223372036854775807
1822 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1823 SHOW COLUMNS IN t1;
1824 Field Type Null Key Default Extra
1825 c bigint(20) YES NULL
1826 c1 bigint(20) YES NULL
1827 c2 bigint(20) YES 9223372036854775807
1828 INSERT INTO t1 (c) VALUES (NULL);
1829 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
1830 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
1831 1 0 0 1
1832 DROP TABLE t1;
1833 #
1834 # SET columns
1835 #
1836 DROP TABLE IF EXISTS t1;
1837 CREATE TABLE t1 (a SET('') <CUSTOM_COL_OPTIONS> NULL,
1838 b SET('test1','test2','test3','test4','test5') <CUSTOM_COL_OPTIONS> NULL,
1839 c SET('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50''51','52','53','54','55','56','57','58','59','60','61','62','63','64') <CUSTOM_COL_OPTIONS> NULL
1840 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1841 SHOW COLUMNS IN t1;
1842 Field Type Null Key Default Extra
1843 a set('') # # #
1844 b set('test1','test2','test3','test4','test5') # # #
1845 c set('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50''51','52','53','54','55','56','57','58','59','60','61','62','63','64') # # #
1846 INSERT INTO t1 (a,b,c) VALUES
1847 ('','test2,test3','01,34,44,,23'),
1848 ('',5,2),
1849 (',','test4,test2','');
1850 Warnings:
1851 Warning 1265 Data truncated for column 'c' at row 1
1852 SELECT a,b,c FROM t1;
1853 a b c
1854 test1,test3 02
1855 test2,test3 01,23,34,44
1856 test2,test4
1857 INSERT INTO t1 (a,b,c) VALUES (0,'test6',-1);
1858 Warnings:
1859 Warning 1265 Data truncated for column 'b' at row 1
1860 Warning 1265 Data truncated for column 'c' at row 1
1861 SELECT a,b,c FROM t1;
1862 a b c
1863 01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50'51,52,53,54,55,56,57,58,59,60,61,62,63,64
1864 test1,test3 02
1865 test2,test3 01,23,34,44
1866 test2,test4
1867 ALTER TABLE t1 ADD COLUMN e SET('a','A') <CUSTOM_COL_OPTIONS> NULL;
1868 Warnings:
1869 Note 1291 Column 'e' has duplicated value 'a' in SET
1870 SHOW COLUMNS IN t1;
1871 Field Type Null Key Default Extra
1872 a set('') # # #
1873 b set('test1','test2','test3','test4','test5') # # #
1874 c set('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50''51','52','53','54','55','56','57','58','59','60','61','62','63','64') # # #
1875 e set('a','A') # # #
1876 ALTER TABLE t1 ADD COLUMN f SET('1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',' ','11','12','13','14','15','16','17','18','19','1a','1b','1c','1d','1e','1f','1g','1h','1i','1j','1k','1l','1m','1n','1o','1p','1q','1r','1s','1t','1u','1v','1w','1x','1y','1z','20','21','22','23','24','25','26','27','28','29','2a','2b','2c','2d','2e','2f','2g','2h','2i','2j','2k','2l','2m','2n','2o','2p','2q','2r','2s','2t','2u','2v','2w','2x','2y','2z','30','31','32','33','34','35','36','37','38','39','3a','3b','3c','3d','3e','3f','3g','3h','3i') <CUSTOM_COL_OPTIONS> NULL;
1877 ERROR HY000: Too many strings for column f and SET
1878 SELECT a,b,c,e FROM t1 WHERE FIND_IN_SET('test2',b)>0 OR a != '';
1879 a b c e
1880 test2,test3 01,23,34,44 NULL
1881 test2,test4 NULL
1882 DROP TABLE t1;
1883 DROP TABLE IF EXISTS t1;
1884 CREATE TABLE t1 (c SET('test1','test2','test3') <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1885 SHOW COLUMNS IN t1;
1886 Field Type Null Key Default Extra
1887 c set('test1','test2','test3') YES NULL
1888 INSERT INTO t1 (c) VALUES (NULL);
1889 SELECT COUNT(c), COUNT(*) FROM t1;
1890 COUNT(c) COUNT(*)
1891 0 1
1892 DROP TABLE t1;
1893 CREATE TABLE t1 (c SET('test1','test2','test3') <CUSTOM_COL_OPTIONS> NULL,
1894 c1 SET('test1','test2','test3') <CUSTOM_COL_OPTIONS> NULL DEFAULT NULL,
1895 c2 SET('test1','test2','test3') <CUSTOM_COL_OPTIONS> NULL DEFAULT 'test2,test3'
1896 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1897 SHOW COLUMNS IN t1;
1898 Field Type Null Key Default Extra
1899 c set('test1','test2','test3') YES NULL
1900 c1 set('test1','test2','test3') YES NULL
1901 c2 set('test1','test2','test3') YES test2,test3
1902 INSERT INTO t1 (c) VALUES (NULL);
1903 SELECT COUNT(c2), COUNT(c1), COUNT(c), COUNT(*) FROM t1;
1904 COUNT(c2) COUNT(c1) COUNT(c) COUNT(*)
1905 1 0 0 1
1906 DROP TABLE t1;
1907 #
1908 # TEXT columns
1909 #
1910 DROP TABLE IF EXISTS t1;
1911 CREATE TABLE t1 (t TEXT <CUSTOM_COL_OPTIONS> NULL,
1912 t0 TEXT(0) <CUSTOM_COL_OPTIONS> NULL,
1913 t1 TEXT(1) <CUSTOM_COL_OPTIONS> NULL,
1914 t300 TEXT(300) <CUSTOM_COL_OPTIONS> NULL,
1915 tm TEXT(65535) <CUSTOM_COL_OPTIONS> NULL,
1916 t70k TEXT(70000) <CUSTOM_COL_OPTIONS> NULL,
1917 t17m TEXT(17000000) <CUSTOM_COL_OPTIONS> NULL,
1918 tt TINYTEXT <CUSTOM_COL_OPTIONS> NULL,
1919 m MEDIUMTEXT <CUSTOM_COL_OPTIONS> NULL,
1920 l LONGTEXT <CUSTOM_COL_OPTIONS> NULL
1921 ) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1922 SHOW COLUMNS IN t1;
1923 Field Type Null Key Default Extra
1924 t text # # #
1925 t0 text # # #
1926 t1 tinytext # # #
1927 t300 text # # #
1928 tm text # # #
1929 t70k mediumtext # # #
1930 t17m longtext # # #
1931 tt tinytext # # #
1932 m mediumtext # # #
1933 l longtext # # #
1934 INSERT INTO t1 (t,t0,t1,t300,tm,t70k,t17m,tt,m,l) VALUES
1935 ('','','','','','','','','',''),
1936 ('a','b','c','d','e','f','g','h','i','j'),
1937 ('test1','test2','test3','test4','test5','test6','test7','test8','test9','test10'),
1938 ( REPEAT('a',65535), REPEAT('b',65535), REPEAT('c',255), REPEAT('d',65535), REPEAT('e',65535), REPEAT('f',1048576), REPEAT('g',1048576), REPEAT('h',255), REPEAT('i',1048576), REPEAT('j',1048576) );
1939 SELECT LENGTH(t), LENGTH(t0), LENGTH(t1), LENGTH(t300), LENGTH(tm), LENGTH(t70k), LENGTH(t17m), LENGTH(tt), LENGTH(m), LENGTH(l) FROM t1;
1940 LENGTH(t) LENGTH(t0) LENGTH(t1) LENGTH(t300) LENGTH(tm) LENGTH(t70k) LENGTH(t17m) LENGTH(tt) LENGTH(m) LENGTH(l)
1941 0 0 0 0 0 0 0 0 0 0
1942 1 1 1 1 1 1 1 1 1 1
1943 5 5 5 5 5 5 5 5 5 6
1944 65535 65535 255 65535 65535 1048576 1048576 255 1048576 1048576
1945 INSERT INTO t1 (t,t0,t1,t300,tm,t70k,t17m,tt,m,l) VALUES
1946 ( REPEAT('a',65536), REPEAT('b',65536), REPEAT('c',256), REPEAT('d',65536), REPEAT('e',65536), REPEAT('f',1048576), REPEAT('g',1048576), REPEAT('h',256), REPEAT('i',1048576), REPEAT('j',1048576) );
1947 Warnings:
1948 Warning 1265 Data truncated for column 't' at row 1
1949 Warning 1265 Data truncated for column 't0' at row 1
1950 Warning 1265 Data truncated for column 't1' at row 1
1951 Warning 1265 Data truncated for column 't300' at row 1
1952 Warning 1265 Data truncated for column 'tm' at row 1
1953 Warning 1265 Data truncated for column 'tt' at row 1
1954 SELECT LENGTH(t), LENGTH(t0), LENGTH(t1), LENGTH(t300), LENGTH(tm), LENGTH(t70k), LENGTH(t17m), LENGTH(tt), LENGTH(m), LENGTH(l) FROM t1;
1955 LENGTH(t) LENGTH(t0) LENGTH(t1) LENGTH(t300) LENGTH(tm) LENGTH(t70k) LENGTH(t17m) LENGTH(tt) LENGTH(m) LENGTH(l)
1956 0 0 0 0 0 0 0 0 0 0
1957 1 1 1 1 1 1 1 1 1 1
1958 5 5 5 5 5 5 5 5 5 6
1959 65535 65535 255 65535 65535 1048576 1048576 255 1048576 1048576
1960 65535 65535 255 65535 65535 1048576 1048576 255 1048576 1048576
1961 ALTER TABLE t1 ADD COLUMN ttt TEXT(4294967296);
1962 ERROR 42000: Display width out of range for 'ttt' (max = 4294967295)
1963 DROP TABLE t1;
1964 DROP TABLE IF EXISTS t1;
1965 CREATE TABLE t1 (c TEXT <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1966 SHOW COLUMNS IN t1;
1967 Field Type Null Key Default Extra
1968 c text YES NULL
1969 INSERT INTO t1 (c) VALUES (NULL);
1970 SELECT COUNT(c), COUNT(*) FROM t1;
1971 COUNT(c) COUNT(*)
1972 0 1
1973 DROP TABLE t1;
1974 DROP TABLE IF EXISTS t1;
1975 CREATE TABLE t1 (c TINYTEXT <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1976 SHOW COLUMNS IN t1;
1977 Field Type Null Key Default Extra
1978 c tinytext YES NULL
1979 INSERT INTO t1 (c) VALUES (NULL);
1980 SELECT COUNT(c), COUNT(*) FROM t1;
1981 COUNT(c) COUNT(*)
1982 0 1
1983 DROP TABLE t1;
1984 DROP TABLE IF EXISTS t1;
1985 CREATE TABLE t1 (c MEDIUMTEXT <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1986 SHOW COLUMNS IN t1;
1987 Field Type Null Key Default Extra
1988 c mediumtext YES NULL
1989 INSERT INTO t1 (c) VALUES (NULL);
1990 SELECT COUNT(c), COUNT(*) FROM t1;
1991 COUNT(c) COUNT(*)
1992 0 1
1993 DROP TABLE t1;
1994 DROP TABLE IF EXISTS t1;
1995 CREATE TABLE t1 (c LONGTEXT <CUSTOM_COL_OPTIONS> NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
1996 SHOW COLUMNS IN t1;
1997 Field Type Null Key Default Extra
1998 c longtext YES NULL
1999 INSERT INTO t1 (c) VALUES (NULL);
2000 SELECT COUNT(c), COUNT(*) FROM t1;
2001 COUNT(c) COUNT(*)
2002 0 1
2003 DROP TABLE t1;