Keep a friend table that holds the UserID and then the UserID of the friend (we will call it FriendID). Both columns would be foreign keys back to the Users table. Somewhat useful example: Table Name: User Columns: UserID PK EmailAddress Password Gender DOB Location TableName: Friends Columns: UserID PK FK FriendID PK FK (This … Read more
Just use a where clause that won’t select any rows: create table xyz_new as select * from xyz where 1=0; Limitations The following things will not be copied to the new table: sequences triggers indexes some constraints may not be copied materialized view logs This also does not handle partitions
PRAGMA table_info(table_name); will get you a list of all the column names.
You have to create a temp table that mirrors the original table’s schema but with the column order that you want, then copy the contents of the original to temp. Delete the original and rename the temp. This is what SQL Management Studio does behind the scenes. With a schema sync tool, you can generate … Read more
Multiple tables help in the following ways / cases: (a) if different people are going to be developing applications involving different tables, it makes sense to split them. (b) If you want to give different kind of authorities to different people for different part of the data collection, it may be more convenient to split … Read more
Itzik Ben-Gan’s example on how to build dynamic PIVOT, I highly recommend his Inside Microsoft SQL Server 2008: T-SQL Programming book — Creating and Populating the Orders Table USE tempdb; GO IF OBJECT_ID(‘dbo.Orders’) IS NOT NULL DROP TABLE dbo.Orders; GO CREATE TABLE dbo.Orders ( orderid int NOT NULL PRIMARY KEY NONCLUSTERED, orderdate datetime NOT NULL, … Read more
This guy asks the same question. He says the same as Frank, but the weightings don’t come out right and in the comments someone suggests using ORDER BY -LOG(1.0 – RAND()) / Multiplier, which in my testing gave pretty much perfect results. (If any mathematicians out there want to explain why this is correct, please … Read more
Just in case anyone still cares: I had the same issue after copying a database directory directly using command cp -r /path/to/my/database /var/lib/mysql/new_database If you do this with a database that uses InnoDB tables, you will get this crazy ‘table does not exist’ error mentioned above. The issue is that you need the ib* files … Read more
The greatest value of an integer has little to do with the maximum number of rows you can store in a table. It’s true that if you use an int or bigint as your primary key, you can only have as many rows as the number of unique values in the data type of your … Read more
Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax. To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty … Read more