Populating a MySQL database with values
I have a locally installed MySQL server on my laptop, and I want to use the information in it for a unit-test, so I want to create a script to generate all the data automatically. I'm using MySQL Workbench which already generates the tables (from the model). Is it possible to use it, or another tool, to create an automatic script to populate it with data?
EDIT: 开发者_高级运维I see now that I wasn't clear. I do have meaningful data for the unit test. When I said "generate all the data automatically", I meant the tool should take the meaningful data I have in my local DB today and create a script to generate the same data in other developers' DBs.
The most useful unit tests are those that reflect data you expect or have seen in practice. Pumping your schema full of random bits is not a substitute for carefully crafted test data. As @McWafflestix suggested mysqldump is a useful tool, but if you want something simplier, consider using LOAD DATA with INFILE, which populates a table from a CSV.
Some other things to think about:
- Test with a database in a known state. Wrap all your database interaction unit tests in transactions that always roll back.
- Use dbunit to achieve the same end.
Update
If you're in a Java environment, dbUnit is a good solution:
- You can import and export data in an XML format through its APIs, which would solve the issue of going from your computer to other members on your team.
- It's designed to restore database state. So it snapshots the database before tests are executed and then restores at then end. So tests are side effect free (i.e. they don't permanently change data).
You can populate with defaults (if defined)
CREATE TABLE #t(c1 int DEFAULT 0,c2 varchar(10) DEFAULT '-')
GO
--This insert 50 rows in table
INSERT INTO #t( c1, c2 )
DEFAULT VALUES
GO 50
SELECT * FROM #t
DROP TABLE #t
精彩评论