Tabel aanmaken (MySQL)

Wat ik ongeveer zoek

create table tmp
(
   sku	     varchar(255)  comment "SKU's standaard 255 tekens",
   sku_oem   varchar(255),
   ean       varchar(40)   comment "Minimaal 40 tekens, ivm. headers",
   note      text          comment "Let op: Text-veld kunnen niet zomaar geïndexeerd worden",

   primary key (sku),
   index(sku_oem)
)
character set utf8

mysqldump

Hoe tabel-definities eruit zien in een mysql-dump:

create table if not exists tbl_content_sjabloon
(
  productnaam    varchar(60)  CHARACTER SET utf8 DEFAULT NULL,
  referentie     varchar(8)   CHARACTER SET utf8 DEFAULT NULL,
  ean            varchar(3)   CHARACTER SET utf8 DEFAULT NULL,
  producttype    varchar(255) DEFAULT NULL,
  beschrijving   text         CHARACTER SET utf8,
  afbeelding     varchar(55)  CHARACTER SET utf8 DEFAULT NULL,
  merk           varchar(255) DEFAULT NULL,
  gewicht        varchar(7)   CHARACTER SET utf8 DEFAULT NULL,
  gewichteenheid varchar(5)   DEFAULT NULL,
  mpn            varchar()    CHARACTER SET utf8 DEFAULT NULL,
  aantalstuks    varchar(29)  CHARACTER SET utf8 DEFAULT NULL,
) 
ENGINE=InnoDB 
DEFAULT CHARSET=latin;

Merk op dat dit er verdacht uitziet: Default charset is latin, maar vrijwel alle kolommen zijn utf8.

PK

create table tbl_main_tool
(
	pk              varchar(100) character set utf8 not null primary key,

	merk            varchar(100) character set utf8 default null,
	apparaat_type   varchar(100) character set utf8 default null,
	apparaat_versie varchar(100) character set utf8 default null,
	soort           varchar(100) character set utf8 default null
);

Auto-incrementele primaire sleutel

create table if not exists tbl_ads
(
     pk         mediumint auto_increment, # Ook bij auto_increment moet je het datatype specificeren
     headline   varchar(25),
     line1      varchar(35),
     line2      varchar(35),
     displayurl varchar(35),
     desturl    varchar(100),
     finalurl   varchar(100),
     memo       varchar(255),

     primary key(pk)
);

Indices

Index specificeren tijdens tabel-definitie:

CREATE TABLE `dwh`.`new_table` (
  `idnew_table` INT NOT NULL,
  `new_tablecol` VARCHAR(45) NOT NULL,
  `new_tablecol1` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idnew_table`),
  INDEX `index2` (`new_tablecol` ASC));

Of later toevoegen:

create table orgbrush_tmp
(
    sku				varchar(255),
    orgbrush_id			text,
    orgbrush_ean		text,
    orgbrush_id_or_ean		text,	# Simple coalesce
    orgbrush_id_and_or_ean	text	# Contains both, if available
);

alter table orgbrush_tmp add index(sku);

Voorbeeld: Gecombineerde PK + indices

drop table if exists device_sku_int;
create table device_sku_int
(
    brand		varchar(255),	# Device brand, e.g. Bosch
    device_type		varchar(255),	# Machinereferentie, e.g. 10GM/P
    device_sku		varchar(255),	# Device SKU, assigned by brand 
    device_kind_nl	varchar(255),	# E.g. "Boormachine"
    device_voltage	varchar(40),
    device_other	varchar(255),
    device_kind		varchar(40),
    device_lang		varchar(40),
    sku			varchar(255),
    cat			varchar(255),	# Just a field from source tables (March 2020)
    note		text,		# Just a field from source tables (March 2020) 
    
    # Primary key
    ##############################################
    #
    primary key(brand, device_type, sku),
    
    # index (sku)
    ##########################
    #
    # * Used in join with root_tmp at sq_prepare_brands_per_sku. This index reduces 
    #   This index reduces run-time of a specifc grouping-query from 7.9s to 0.08s!
    # * Although sku is part of the PK, it doesn't function as an index, as it isn't
    #   the first field in the PK
    #
    index (sku),

    # index (device_kind_nl)
    ##########################
    #
    # Near the end, emtpy instances of this field are filled with a value
    # and that takes a couple of seconds (without index)!
    #
    index (device_kind_nl)
    
    # Test: Does this improve performance? > Makes it worse!
    ########################################################
    #
    # index (device_type),
    # index (device_sku),
    # index (brand),
    # index (device_kind)
);

Commentaar

create table blub_tmp
(
   sku    varchar(255) comment 'Dit is het commentaar-veld',
   blub   varchar(255),
   note   text,

   primary key (sku),
   index (blub)
)

NN - not null

brand en device_type hebben allebei eigenschap not null. Ze bevatten inderdaad geen NULL-waardes. Maar kunnen wel leeg zijn

Indien je specificeert dat een veld niet null mag zijn, kreeg je daar geen ongedefineerde waardes. Maar mogelijk wel lege velden krijgen. Bv.:

drop table if exists device_sku_int;
create table device_sku_int
(
    brand		varchar(255) not null,	# Device brand, e.g. Bosch
    device_type		varchar(255) not null,	# Machinereferentie, e.g. 10GM/P
    device_sku		varchar(255),		# Device SKU, assigned by brand 
    device_kind_nl	varchar(255),		
    device_voltage	varchar(40)
);

Charset & collation

[1]:

create table blub
(
   pk   varchar(40),
   v2   varchar(255)
)

Zie ook

• Indexes (MySQL)

Bronnen

• http://dev.mysql.com/doc/refman/5.1/en/create-table.html
• https://mysqlserverteam.com/mysql-8-0-collations-migrating-from-older-collations/