The Field API, introduced in Drupal 7, allows data fields to be attached to entities with the API taking care of data storage, loading, update, and rendering. When a new field is created, a table is created for it in the database. If the entity it is attached to supports revisions, then a second table is created to store versions of the field content.
As you start typing a label for a new field, a machine name is generated starting with field_. Spaces and non-alphanumeric characters are converted to underscores, e.g., First name: field_first_name.
The table name is then generated from a concatenation of the entity name, a separator, and the field's machine name. For the data table, the separator is __ while the revision table is _revision__. If the "First name" field is for a node, then we have the node__field_first_name and node_revision__field_first_name tables.
There is a 48-character string limit, keeping a 16-character margin for database prefixes so as not to exceed 64 characters altogether. When creating a field from the UI, data validation will not allow you to exceed this limit. If the field is being created programmatically, then there will be an error when the time comes to create the field.
There are times when you want to be able to change the machine name. For example, when you have brackets at the end of a label, e.g., "Address (2)", you end up with a trailing "_" character — field_address_2_.
Perhaps, you simply want to change the machine name, especially when it is too long and it has been truncated. Clicking the "Edit" link in front of the machine name allows you to do just that as shown in the following:
The field_ prefix, for historical reasons, is used to namespace fields managed through the UI to the Field module. Fields without the prefix are equally valid, e.g., the body field added to nodes by Drupal core itself. You can save six characters by not prefixing field names at all. For Drupal 7, the Remove field name prefix module does just that.
In Drupal 8, this prefix can be removed or replaced by setting the field_prefix key of the field_ui.settings config. This can be done with:
drush config-edit field_ui.settings opens the field_ui.settings.yml file in your text editor, where you can change the value of field_prefix and save it. $config = \Drupal::configFactory()->getEditable('field_ui.settings');
$config->set('field_prefix', '')->save();
Sometimes, when looking through your database, you may find some tables with weird names such as block_content_r__6991b3347, field_deleted_data_ee4ee4e3e4, paragraph_r__d641f80a33 etc. These are either of the following:
When a field is deleted, the table is renamed to the pattern of field_deleted_data_FIELD_UUID. Since there is a 64-character limitation on field names, the unique storage identifier is hashed, and the first ten characters are taken as the FIELD_UUID. The actual removal of the table from the database happens through a different process.
Sometimes, fields with too long names are programmatically-created, and the field table name must be safe and unambiguous. The entity type identifier is truncated to 34 characters. A shorter separator (__ or _r__) is used together with the first ten characters of a hash of the unique storage identifier of the field. These are concatenated together to get the weird-looking names for the field table.
The code for the name generation is in the Drupal\Core\Entity\Sql\DefaultTableMapping class:
public function getDedicatedRevisionTableName(FieldStorageDefinitionInterface $storage_definition, $is_deleted = FALSE) {
if ($is_deleted) {
// When a field is a deleted, the table is renamed to
// {field_deleted_revision_FIELD_UUID}. To make sure we don't end up with
// table names longer than 64 characters, we hash the unique storage
// identifier and return the first 10 characters so we end up with a short
// unique ID.
return "field_deleted_revision_" . substr(hash('sha256', $storage_definition->getUniqueStorageIdentifier()), 0, 10);
}
else {
return $this->generateFieldTableName($storage_definition, TRUE);
}
}
protected function generateFieldTableName(FieldStorageDefinitionInterface $storage_definition, $revision) {
$separator = $revision ? '_revision__' : '__';
$table_name = $storage_definition->getTargetEntityTypeId() . $separator . $storage_definition->getName();
// Limit the string to 48 characters, keeping a 16 characters margin for db
// prefixes.
if (strlen($table_name) > 48) {
// Use a shorter separator, a truncated entity_type, and a hash of the
// field UUID.
$separator = $revision ? '_r__' : '__';
// Truncate to the same length for the current and revision tables.
$entity_type = substr($storage_definition->getTargetEntityTypeId(), 0, 34);
$field_hash = substr(hash('sha256', $storage_definition->getUniqueStorageIdentifier()), 0, 10);
$table_name = $entity_type . $separator . $field_hash;
}
return $table_name;
}
Guessing the contents of tables with normalized names is difficult. Inspecting such a table with a GUI tool such as Sequel Pro, PhpMyAdmin, Adminer, MySQLWorkbench, or any similar tool will show that the last columns include the field name in a pattern depending on the type of field or data the table holds:
FIELD_NAME_valueFIELD_NAME_value and FIELD_NAME_formatFIELD_NAME_target_idFIELD_NAME_uri, FIELD_NAME_title and FIELD_NAME_optionsThe Drupal\Core\Entity\Sql\DefaultTableMapping class has useful methods. First, you need to get the entity manager service from the service container:
$entity_manager = \Drupal::getContainer()->get('entity.manager');
Assuming we are interested in the names of all field data and revision tables for the node entity type:
// Get an instance of Drupal\Core\Entity\Sql\DefaultTableMapping class
$table_mapping = $entity_manager->getStorage('node')->getTableMapping();
$table_names = $table_mapping->getDedicatedTableNames();
To get the data storage tables for a specific field, we need field storage definitions:
$storage_definitions = $entity_manager->getFieldStorageDefinitions('node');
$storage_definitions is an array of Drupal\field\Entity\FieldStorageConfig objects keyed by field names.
For example, to get information about a field called field_news_category:
// node__field_news_category
$data_table = $table_mapping->getDedicatedDataTableName($storage_definitions['field_news_category']);
// node_revision__field_news_category
$revision_table = $table_mapping->getDedicatedRevisionTableName($storage_definitions['field_news_category']);
The DefaultTableMappingClass can also tell us the mapping of field columns to table columns among other things.
When strange-looking tables suddenly appear in a database, they are there for a good reason. A look at the table structure will reveal the name of the field that stores the data they hold.