Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 27 10 Older Entries

Displaying posts with tag: it (reset)

Always use decimal for money
+0 Vote Up -0Vote Down

Data types make all the difference in the world when you’re designing your database. The choices you make now will affect the quality of your data, as well as application performance. I’m going to focus on one issue in this article: why you should always use decimals to represent money. Let’s jump in and see why that’s true.

An example of floats gone wrong

Let’s use a really, really simplified accounting ledger. It’s just three fields, an entry id, a customer id, and an amount, which has a float for a data type.

Create Table LedgerEntries
 LedgerEntryID Int Primary Key Auto_Increment Not Null
,CustomerID Int Not Null
,Amount Float Not Null

Now let’s load some data.

Insert Into LedgerEntries (CustomerID, Amount)
  [Read more...]
Interviewing for a Database Developer
+0 Vote Up -0Vote Down

I work for a firm that’s heavily invested in SQL – a team that needs to have developers who know their way around relational databases and MySQL in particular. I want to show you how I run interviews for our development positions.


Everybody has their own methods and opinions on how to conduct technical interviews. I’ve found that I generally dislike interviews that focus either on whiteboard puzzles or obscure technical details, since they don’t really show how well the candidate is at what really matters: building functioning, quality apps. I really like running the interview like we’re talking about the design for a new product. I want to figure out the requirements, mull over the data model, and write some simple queries to make sure we can show the

  [Read more...]
Inner vs. Outer Joins
+1 Vote Up -0Vote Down

I want to teach you the difference between an inner and an outer join. We first need to think about what a join is. Simply, it’s when you combine two tables to make a new one. You’re not physically creating a new table when you join them together, but for the purposes of the query, you are creating a new virtual table. Every row now has the columns from both tables. So if TableA has columns Col1 and Col2 and TableB has columns Col3 and Col4, when you join these two tables, you’ll get Col1, Col2, Col3, and Col4. Just as with any query, you have the option of including all columns or excluding some, as well as filtering out rows.

Inner join. A join is combining the rows from two tables. An inner join

  [Read more...]
Driverless cars and MySQL
+0 Vote Up -0Vote Down

Unless you’ve been living under a rock, you’ve probably heard about driverless cars. Google and the large car makers have been creating cars that doen’t need a human driver. This is tremendous news. Over 30,000 people die in car crashes every year in the US. While not all of those could be prevented by removing the meatbag driver, a lot of them could. Driverless cars are the right way forward and I can’t wait until they come. Every time I see a car wreck or hear about a drunk driver, I wish we had this

  [Read more...]
Difference between strict_all_tables and strict_trans_tables
+0 Vote Up -0Vote Down

MySQL has default behavior that’s just plain wrong. I’ve covered some quirks with MySQL not null behavior and offered up using the SQL Mode strict_all_tables as a solution.

As a review, SQL Mode changes the way MySQL executes SQL statements and it’s often used to make MySQL behave. There are several switches that I would turn on by default, like only_full_group_by. You can change settings for yourself (just your connection) or for everybody (change it at the server level).

There’s another SQL Mode option, strict_trans_tables, that’s similar in intent but not in behavior to

  [Read more...]
Intro to MySQL Information Schema
+0 Vote Up -0Vote Down

Databases store information, right? Well, what if they could store information about your information so you could query it? Good news! Most database engines such as MySQL implement Information Schema, or a set of views that describe your tables and columns. If you’ve ever used a language like Java or C#, you might be familiar with reflection: the ability to read an object’s metadata. This enables you to do meta-programming, or writing logic about the program itself.

The idea of Information Schema is like reflection: they are views in your database that you can use in your programs or scripts when you need to know what the schema looks like or check the health of the instance. Unlike Show Processlist or Desc Table commands, the results are in tabular format.

I’m going

  [Read more...]
MySQL 5.6 Optimizer Trace
+5 Vote Up -0Vote Down

One of the new features of MySQL 5.6 is the optimizer trace. What does it do? Well, you know Explain, right? Just add Explain before a query and MySQL will print out how it would execute the query. The optimizer trace does that, except it gives you a little more background into to how it makes its decisions. Think of it as “It’s a Wonderful Life” for indexes – it shows you what the world would be like without that primary key or index.

If you ask, why did MySQL choose to run a full table scan vs. an index seek, the optimizer trace can help you get a better picture of the situation. The other big deal with the optimizer trace is that it’ll show you the relative costs of operations. Yeah, MySQL already tells you how many rows it’ll have to read, but now you know how much those cost

  [Read more...]
Creating Users & Granting Permissions in MySQL
+0 Vote Up -0Vote Down

One of the first tasks when setting up a new MySQL server is creating users and granting them permissions, or giving them the ability to do stuff in MySQL. By default, a single user, the root user, is created when you setup MySQL. This user is granted all privileges on the entire system, which means you should create separate logins for administrators and applications. Let’s walk through all the basic steps you need to create a new user and give them permissions.

Create a User

The first command I’ll show you is pretty simple:

Create User bob@'%' Identified By 'Astr0ngPhr@$e';

Before we move on, let’s check that user’s permissions:

Show Grants for bob@'%';

Which should show something like:

  [Read more...]
Designing Multi-Tenanted Databases – Anti-patterns
+0 Vote Up -0Vote Down

In this post, I’m going to briefly cover the signs that you’re doing multi-tenancy wrong. Some of these practices are entrenched in software: there are gems in Ruby on Rails, for instance, use the first anti-pattern to achieve multi-tenancy. Listen, you can drive a car with a flat tire and you can eat yogurt with a fork. People have made these solutions work, but there’s a better way.

Creating tables or schemas per customer

If you find yourself running DDL (Create Table…) for each new company or user that you add to your system, most likely you’re committing a pretty big anti-pattern. Now every time you update the

  [Read more...]
Designing Multi-Tenanted Databases – Many-to-Many
+0 Vote Up -0Vote Down

I want to give you tools that you can use for building databases that can handle complex relationships. In the previous article in this series, I looked at hierarchical data. The classic example of a hierarchy is an org chart. On most org charts I’ve seen, an employee has only one boss, which is a one-to-many relationship. The other kind of segmentation is many-to-many. A good instances of this is your social circle. Most people have many friends, and those friends have multiple friends themselves.

This is a common pattern. When developing a multi-tenanted application, users

  [Read more...]
Showing entries 1 to 10 of 27 10 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.