why is it important to recommend default values and lengths of mysql data on table creation?

If I do it using php instead, how does it benefit me making sure that I have defined them 开发者_JAVA百科in the creation of the mysql table?

---

Default values are useful

So that if you do an insert you don't have to state each and every field explicitly.

CREATE TABLE test(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  type CHAR(1) NOT NULL DEFAULT 'F',
  invoicenumber VARCHAR(45) NOT NULL,
  count INT(11) NOT NULL DEFAULT 1);

Now if I do an insert I need only do this

INSERT INTO test (invoicenumber) values ('20110125');

or this, the two are the same

INSERT INTO test VALUES (null,default,'20110125',default);

If I do a select I get

SELECT * FROM test;

+----+------+---------------+-------+
| id | type | invoicenumber | count | 
+----+------+---------------+-------+
|  1 | 'F'  | '20110125'    | 1     |
+----+------+---------------+-------+

The id is assigned an auto_incrementing id (starting at 1,2,3,4,...)
the type = 'F' default
the count = 1 default
Only the invoicenumber I must supply, I cannot forget, because I stated that it must be filled. not null

Stating the length of fields is useful
Because the shorter your fields are the faster your queries will run and the less network traffic and harddisk space you need. (oversimplification, but good rule of thumb)

---

If I do it using php instead, how does it benefit me making sure that I have defined them in the creation of the mysql table?

You define your table structures independently of what you use at your application.

I can make sure that input on a form is under the string length of say 15 characters, why does it benefit me setting the max-length to 15 in phpmyadmin.

1. You don't define a max-length in phpmyadmin. You define that in DDL via phpmyadmin. This is an important distinction to make if you really want to understand what the heck you are doing.

2. What prevents you (or someone working on your code) from entering a bug, changing the string length restriction in your form (or completely removing it by accident)? What happens then?

Anyways, you still have to define the size of your database table fields no matter what. Your database is your model, your data model, not your web forms. Your web forms work off it.

Taking the database defaults is just laziness and horrible design, (not to mention inefficient.) Say you are using an admin tool like phpmyadmin and that thing by defaults sets your database fields to, say, length 255. And yet your actual data fields will be no larger than 15, are you willing to waste all that just because your form defines a limit?

Your form defines a limit on what the user can enter (and send to the back end). But that will get stored in database fields that are much larger than that if you don't define their sizes as well. Food for thought.

---

It doesn't. Keep it simple; set defaults one place or the other. And only if there's a logically legitimate default value. Don't make up something just to have one.

The application needs to drive the tool, not vice versa.

Lots of application control battles between database admins and application developers get fought this way. Try to get this one cleared up early to avoid both code and people conflicts.

It's worth spending some time thinking about and it's easy to reflexively install some funny stuff. One app I'm workng on has most of the string fields set to NOT NULL with a default of "" (empty string), and then adds a lot of code to detect and flag errors on empty strings.