Using both a GUID and an auto-incrementing integer

I always tend to use surrogate primary keys in my database.That is: those primary keys have no actual meaning in the problem domain, and thus, those primary keys are never exposed to users.(If this surrogate primary key is of type GUID or an identity, I don't care; this depends on the requirements).

If you say that users should be able to identify objects based on a user-friendly ID, then, I think that this user-friendly ID is a value that belongs to your 'problem domain'.This means, that this ID should indeed be an attribute in your table, but it should not be used as the primary key in your table.

This also allows you to easily modify the value of such an user-friendly ID (if that should be necessary), without you having to worry about modifying related foreign keys as well.

"Why do "users should be able to identify objects based on a user-friendly ID" ?

In my opinion, your users should itentify records using codes.

Let's say your database contains products (as you mentionned it in Question). Wouldn't it be better if they had codes to represent products, that the users could enter.

Let's say you have tables and chairs, as a user, i would prefer using tbl and chr than 1 and 2 to identify what I am talking about.

In MySQL, you'll need to set your numeric ID as a PRIMARY KEY, as AUTO_INCREMENT may be only the PRIMARY KEY, which means it should also be NOT NULL.

You can still define a UNIQUE INDEX on your GUID column and use it anywhere, though an InnoDB table will be clustered on the numeric id, not on the GUID.